Implement Time Intelligence using DAX
It is common for business users to want to aggregate metrics—for example, revenue—across time, such as year-to-date revenue for a certain date, or prior-year revenue for the comparable period. Fortunately, DAX has a family of functions, referred to as Time Intelligence, that facilitate such calculations.
All Time Intelligence functions require a calendar table that has a date type column with unique values. If the date column is not part of a relationship, the calendar table must be marked as a date table, which can be done as follows:
Go to the Report or Data view.
Select the calendar table in the Fields pane.
On the Table tools ribbon, select Mark as date table > Mark as date table.
Select the date column from the Date column dropdown list.
Select OK.
Note Different Calendars
The Time Intelligence functions in DAX only support the Gregorian calendar. If you use a different kind of calendar—such as a 4-4-5, which is common in retail, or a weekly calendar—then you’ll need to use custom calculations. These types of calculations are out of the scope of this book.
Most Time Intelligence functions return tables that can be used as filters in CALCULATE. For example, you can use the DATESYTD function to calculate a year-to-date amount as follows:
Profit YTD =
CALCULATE(
[Total Profit],
DATESYTD(‘Date'[Date])
)
You can also combine Time Intelligence functions. For example, to calculate year-to-date profit for the previous year, use the following formula:
Click here to view code image
Profit PYTD =
CALCULATE(
[Profit YTD],
DATEADD(‘Date'[Date], -1, YEAR)
)
Some Time Intelligence functions, such as DATESYTD, can accommodate fiscal years. For example, if you had a fiscal year ending on June 30, you could calculate profit year-to-date for the fiscal year as follows:
Click here to view code image
Profit FYTD =
CALCULATE(
[Total Profit],
DATESYTD(‘Date'[Date], “30-6”)
)
The Total Profit, Profit YTD, Profit PYTD, and Profit FYTD measures can be seen together in Figure 2-26.
FIGURE 2-26 Time Intelligence calculations
Notice how the Profit YTD measure shows the cumulative total profit within each year. The Profit PYTD measure shows the same values as Profit YTD one year before. Profit FYTD shows the cumulative total profit for fiscal years, resetting on July 1 of each year.
Need More Review? Time Intelligence In DAX
DAX includes over 30 Time Intelligence functions. Full details on all Time Intelligence functions are out of the scope of this book. For more details, see “Time intelligence functions” at https://docs.microsoft.com/en-us/dax/time-intelligence-functions-dax.
