5 ways to Calculate Last 12 Months in DAX for Power BI

It is common in financial reports to calculate the last 12 months in DAX. With it, Power BI does a rolling 12 months calculation from the selected date. This can be achieved in different 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

• Last 12 Month Calculation
• How to Calculate Last 12 Months
  • DATESINPERIOD
  • DATESBETWEEN
  • Manual using INDEX
  • Manual using EDATE
  • Manual using LASTDATE
• Conclusion

Last 12 Month Calculation

Below is a picture that shows what we want to achieve. It has 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, also known as a rolling 12-month calculation.

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 are different ways to do it

How to Calculate Last 12 Months

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.

DATESINPERIOD

To get a rolling 12 months, 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:

1. a column reference containing dates
2. a base date
3. the number of intervals it needs to shift, starting from the base date (positive or negative)
4. the time shift needed starting from the base date (day, month, quarter or year)

The 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
      )
)

DATESBETWEEN

A second way to filter the last 12 months period is by using the DATESBETWEEN function. To make this function work, the three arguments you put in are:

1. a column reference containing dates
2. a start date
3. 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 example below 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.

Manual using INDEX

The third method is a great example of 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. And with it your calculation shows the trailing 12 months.

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

Manual using EDATE

The fourth method arrives at the latest 12 months by using the EDATE function to retrieve the minimum date. This function:

1. takes a starting date as its first argument
2. 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

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, which requires a table as input.

Results 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

Conclusion

As this article showed, there are different ways to achieve the same result. Next to using in-built time intelligence functions, knowing how to achieve a result manually helps you understanding DAX. The manual methods may be more verbose to write, yet help in understanding how you can change filter context.