DAX has a variety of time-intelligence functions that help you to build and compare calculations over different time period. With the help of these functions, you can see calculations over days, weeks, months, quarters and years.
Let’s take a closer look. In this video, we’ll show how to use the SAMEPERIODLASTYEAR, YTD and MTD functions inside the CALCULATE.
Basic time-intelligence functions
The SAMEPERIODLASTYEAR function shifts dates one year back in time from the dates in the specified dates column. It’s best to use the function inside CALCULATE:
Last year sales = CALCULATE ( SUM( Sales[Sales Amount]),
SAMEPERIODLASTYEAR (Dates[Date]))
However, to find out the past sales in a table we believe it’s better to use the DATEADD function. This function moves the given set of dates by a specified interval. This provides more flexibility, as you can choose how far back you’d like to calculate your values.
Last six month sales = CALCULATE ( SUM( Sales[Sales Amount]),
DATEADD (Dates[Date]), -6, MONTH)
Last 14 days sales = CALCULATE ( SUM( Sales[Sales Amount]),
DATEADD (Dates[Date]), -14, DAYS)
The DATESYTD function returns a table that contains dates for the year to date. If you have a table with months of a year as rows, the function will give you a total sum of all values. When filtered by a year, this will give you the running total of the sales.
The DATESMTD function will return a table that contains a column of the dates for the month to date.
Calendar table for time-intelligence functions
It is important to remember that before you can use any DAX time-intelligence functions, you must have a proper date dimension in your data model (e.g. a calendar table).
The easiest way to quickly create a calendar is to use the function CalendarAuto. It will automatically take the first and last dates from your data and produce a calendar. However, using the CalendarAuto function is a bad practice, as in some cases it may lead to mistakes in your data model.
That is why it is better to use the Calendar function:
TableName = Calendar ([Start Date], [End Date]).
Watch one of our previous videos to learn how to build a calendar table using DAX.