It is common in financial reports to calculate the last 12 months in DAX. This can be achieved in a couple of ways. Each way requires changing the filter context. And knowing how to do this proves useful in many time intelligence calculations. The goal of this post is to show different ways to approach this.
Table of contents
Below is a picture that shows what we want to achieve. There’s a column that shows the Total Sales split out by year and month. The column next to it shows the value of the last 12 months based on the filter context of each cell.
For example in this picture, the first month of 2011 includes the last month of 2010. Notice how the Total Sales full year 2011 number also shows in the December 2011 cell as Last 12 Months calculation.
The above is what the outcome should look like, and there’s different ways to do it
Allow me to show you 5 different ways to calculate the last 12 months in a measure. For each method, we will use the CALCULATE function to change the filter context. The first argument in CALCULATE is an expression, whereas each argument after allows you to add a filter condition. Let’s dive in.
Method 1: DATESINPERIOD
To start with, the first method makes use of the DATESINPERIOD function. This is a time intelligence function that allows you to shift the period used. As arguments, this function needs:
- a column reference containing dates
- a base date
- the number of intervals it needs to shift, starting from the base date (positive or negative)
- the time shift needed starting from the base date (day, month, quarter or year)
Below DATESINPERIOD function below uses the (1) date column from the date table, and starting from the (2) latest date in the current filter context, it shifts the period (3) back by 12 intervals and (4) indicates the interval should be in months.
Last 12 Months V1 = CALCULATE ( [Total Sales], DATESINPERIOD ( 'Date'[Date], -- returns period from date column MAX ( 'Date'[Date] ), -- starting from MAX date -12, -- shifting it back 12 intervals MONTH -- each interval being a month ) )
Method 2: DATESBETWEEN
A second way to determine a 12 month period is using the DATESBETWEEN function. To make this function work, the three arguments you put in are:
- a column reference containing dates
- a start date
- an end date
The difference with the previous example is that this function needs an actual start date, instead of a period shift from the end date. The StartDate variable in below example retrieves the starting date, by shifting the EndDate back 1 year using the EDATE function and then adding a day.
Last 12 months V2 = VAR EndDate = MAX( 'Date'[Date] ) -- retrieves MAX Date VAR StartDate = EDATE( EndDate, -12 ) + 1 -- shifts EndDate to year beginning VAR Result = CALCULATE( [Total Sales], -- retrieves the relevant date range DATESBETWEEN( 'Date'[Date], StartDate, EndDate ) ) RETURN Result
The following three methods use a similar structure. The difference is in the way they retrieve the different date values. Let’s jump right in.
Method 3: Manual using INDEX
The third method is a great example on how you can manually shift your period to the desired range. So no built in time intelligence functions! For this to work, your date table needs a column containing an index number. This index number increments with each change in Year/Month and does not reset with a change of years.
The calculation contains a variable with the latest IndexYearMonth number in the current filter context, named MaxIndex. We then calculate the MinIndex by subtracting 12 from this index number. Lastly, after removing a filter on the Date table, you input the period you want to return.
Last 12 months v3 = VAR MaxIndex = MAX( 'Date'[IndexYearMonth] ) -- Ending period VAR MinIndex = MaxIndex - 12 -- Starting period VAR Result = CALCULATE( [Total Sales], -- return dates between starting and ending period FILTER( ALL( 'Date' ), 'Date'[IndexYearMonth] <= MaxIndex && 'Date'[IndexYearMonth] > MinIndex ) ) RETURN Result
Method 4: Manual using EDATE
The fourth method makes use of the EDATE function to retrieve the start of the year. This function:
- takes a starting date as its first argument
- and shifts this date back or forward a number of months.
The formula to use then is:
Last 12 months v4 = VAR MaxDate = MAX( 'Date'[Date] ) -- retrieve latest date VAR MinDate = EDATE( MaxDate, -12 ) -- move it back 12 months VAR Result = CALCULATE( [Total Sales], FILTER( ALL( 'Date' ), -- return period between 'Date'[Date] <= MaxDate && -- latest date 'Date'[Date] > MinDate ) -- bigger than year before ) RETURN Result
Method 5: Manual using LASTDATE
Last but not least, method 5 makes use of the LASTDATE function in DAX. The function returns a table as its result. And with that, this same value can be used in the SAMPERIODLASTYEAR function, that requires a table as input.
Resulting in the fifth last 12 months calculation:
Last 12 months v5 = VAR MaxDate = LASTDATE( 'Date'[Date] ) -- get latest date VAR MinDate = SAMEPERIODLASTYEAR( MaxDate ) -- shift back 1 year VAR Result = CALCULATE( [Total Sales], FILTER( All( 'Date' ), -- return the dates between 'Date'[Date] <= MaxDate && -- end of year and 'Date'[Date] > MinDate ) -- beginning of year ) RETURN Result
As before shows, there are different ways to achieve the same result. Next to using in-built time intelligence functions, knowing how to manually achieve a result helps you in understanding DAX. The manual methods may be more verbose to write, yet help in understanding how you can change filter context.