Key date and time intelligence function examples on common use cases
What
Power BI DAX has a long list of different Date and Time Intelligence related functions. However, by pareto principle a couple of them will solve most use cases. In this post is reviewed DAX date and time intelligence function theory and usage examples.
Theory
The business goal is typically to calculate and filter based on dates. From DAX point of view, I make the following simplified division: A) Filtering data with dates and B) Data selection with time intelligence.
A) Filtering data with dates (DAX date functions). Result is displayed for the selected dates. You cannot filter out the months behind the values you are displaying or you will not see the data.
Consider that all of the basic date selection can be done in Power BI Power Query to the calendar table instead of using DAX. See my post Power BI Financial Date Table. Typically, same date patterns repeat in multiple measures. Furthermore, with Power Query, the load of the data happens when the report updates. Therefore, it really makes sense to do a lot of date operations to calendar table with Power Query and use DAX in only special individual cases.
B) Data selection with time intelligence (DAX Time Intelligence related functions). Calendar dates are displayed for the selected calendar period, but the data is from the period defined by the time intelligence function. As an example, below screenshot yellow circled data, compare 2002 sales at the left to 2001 sales at the right).
Time Intelligence functions typically return tables or table filter expressions. E.g. CALCULATE command can use these table filter expressions. Therefore, Time Intelligence functions do not provide the best calculation performance to figure out a static date value. So prefer MIN(date) and MAX(date) returning values, not LASTDATE(date) or FIRSTDATE(date) if you do not use dates as a filter or table filter expression!
As default e.g. MAX(date) will not work as a filter in the CALCULATE function as it returns a value, not a table. However, there is a workaround with variables if you want to use date value as a filter:
Measure last selected month sales sum with variables =
VAR end_date_value = max('Calendar'[Start of Month])
VAR end_date_filter = FILTER(ALL('Calendar');Calendar[Start of Month]= end_date_value)
RETURN CALCULATE(SUM(Sales[SalesAmount]);end_date_filter)
In this case, the time intelligence function below will deliver the same result. However, in more complex scenarios, the approach above with variables can be useful.
Measure last selected month sales sum with time intelligence = CALCULATE(SUM(Sales[SalesAmount]); LASTDATE(Calendar[Start of Month]) )
Above ALL function returns all the rows in the calendar table, ignoring any filters that might have been applied. This function is useful for clearing filters and creating calculations on all the rows in a table.
How
When using DAX Time Intelligence functions, it is always recommended or required to have a continuous calendar without a break in the dates. The calendar table should be marked as a date table. You can do this by right clicking the calendar table and selecting mark as date table (screenshot below).
Code
Here are listed some useful DAX date calculation patterns for A) Filtering data with dates and B) Data selection with time intelligence.
Definitions
You can replace SUM(Sales[SalesAmount]) with any measure you are using.
Calendar[Start of Month]: first calendar day of each month
A) Filtering data with dates
The basic pattern is CALCULATE based on a measure and FILTER on dates. DATE can be used to set start or end date for an interval.
Measure Sales YTD excluding current month=
CALCULATE(SUM(Sales[SalesAmount]);
FILTER('Calendar';Calendar[Start of Month]<DATE(Year(today());MONTH(today());1));
FILTER('Calendar';YEAR(Calendar[Date])=YEAR(today())
)
Measure Sales YTD previous year excluding current month =
CALCULATE(SUM(Sales[SalesAmount]);
FILTER('Calendar';'Calendar'[Month Number]<MONTH(today()));
FILTER('Calendar';YEAR(Calendar[Date])=YEAR(today())-1)
)
Measure month sales two months ago =
CALCULATE(SUM(Sales[SalesAmount]);
FILTER('Calendar'; Calendar[Start of Month]=
DATE(YEAR(TODAY());MONTH(EOMONTH(TODAY();-2));1))
)
EOMONTH is used to select 2 months backward from the current month. You do not want have a month number -1 or 13!
DAX ADDDATE function (SQL has DATEADD() so this causes often confusion) returns a table, not a date value, so it does not work here when iterating months.
B) Data selection with Time Intelligence
The basic pattern is CALCULATE based on a measure and a time-intelligence function. With DATEADD and PARALLELPERIOD functions one can solve almost all date related comparisons.
Measure last selected month =
CALCULATE(SUM(Sales[SalesAmount]);
LASTDATE(Calendar[Start of Month])
)
Measure previous month =
CALCULATE(SUM(Sales[SalesAmount]);
DATEADD(Calendar[Start of Month];-1;MONTH)
)
Measure same period previous year =
CALCULATE(SUM(Sales[SalesAmount]);
DATEADD(Calendar[Date];-1;YEAR)
)
Measure same period two year ago =
CALCULATE(SUM(Sales[SalesAmount]);
DATEADD(Calendar[Date];-2;YEAR)
)
Measure previous month - current month on a monthly sales data table =
CALCULATE(SUM(Sales[SalesAmount]);
DATEADD(Calendar[Start of Month];-1;MONTH)
)
- SUM(Sales[SalesAmount])
Measure selected quarter previous year =
CALCULATE(SUM(Sales[SalesAmount]);
PARALLELPERIOD('Calendar'[Date];-4;QUARTER)
)
Measure last selected quarter previous year =
CALCULATE(SUM(Sales[SalesAmount]);
PARALLELPERIOD(LASTDATE('Calendar'[Date]);-4;QUARTER)
)
Note: Do not put Time Intelligence function inside FILTER function (inside the CALCULATE function), then it will just filter the calendar table and the visible selected dates. It will not do the data display with time intelligence. If you need to just filter date values inside FILTER function you are most likely able to do that with date functions, instead of more resources consuming time intelligence functions.
As written by Microsoft on DAX: “The PARALLELPERIOD function is similar to the DATEADD function except that PARALLELPERIOD always returns full periods at the given granularity level instead of the partial periods that DATEADD returns. For example, if you have a selection of dates that starts at June 10 and finishes at June 21 of the same year, and you want to shift that selection forward by one month then the PARALLELPERIOD function will return all dates from the next month (July 1 to July 31); however, if DATEADD is used instead, then the result will include only dates from July 10 to July 21.“
Therefore, if both LAST DATE and PARALLEL PERIOD functions are used, in the above formula ‘Measure selected quarter previous year‘ all the previous quarter sales are shown instead of just up to the max existing sales date of the selected quarter.
Sample Power BI file
Measures are created under the sales table in the example file: https://drive.google.com/file/d/1douXjNV-V8JVdhJb_M4czVR14uQ8fHR-/view?usp=sharing
Sample data source: https://www.learndax.com/power-bi-sample-data-for-beginners-to-download/