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. 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.

Introduction

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

Methods

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:

  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)

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:

  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 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:

  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

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.

Rick de Groot
About Rick de Groot

Rick is the founder and editor of BI Gorilla. He believes learning is one of the great pleasures in life and wants to share his knowledge to help you improve your skills.

Learn more about him here, connect with him on Twitter, Facebook and LinkedIn and subscribe to his YouTube Channel.

1 thought on “5 ways to calculate last 12 months in DAX for Power BI”

  1. Interesting read! I have an almost similar problem, except that in my case, I need to compute a rolling total over 12 months, starting from the end of month of the month before the current month (now is 2021-06-22, so starting 2021-05-31 backwards for 12 months, and so on, skipping 1 month at a time, 2021-05, 2021-04, 2021-03, etc… until the month that is the first date in a date range slicer).

    One additional complication is that I also have a slicer to let the users filter dates by day type, in other words, showing a rolling total of some volume only for weekdays, or weekends, or both.

    For some reason, when I use DAYSINPERIOD, the function never seems to be affected by the day type slicer choice. I use it to filter a CALCULATE over a measure computing a volume, ie CALCULATE always computes the measure over all the dates in the rolling 12 months period, no distinction seems possible between weekdays and weekends, as if the slicer did not exist.

    BUT when I just use the measure itself on the same table visual, for ex, the day type slicer choice is recognized and the measure computes accordingly.

    I even enclosed the DATESINPERIOD function call inside a FILTER call, like so:

    FILTER(
    DATESINPERIOD(DimDate[Date], __last_date, – __month_in_period, MONTH),
    DimDate[Date]
    )

    and it still computes the wrong numbers.

    This is all utterly incomprehensible. No common sense logic seems to apply when it comes to DAX 😉 .

    So here is my question: I tried your method 3 but do not understand why do you remove all filters with ALL()?

    If I do that, then the slicer selection for weekend or weekdays will be ignored.

    In any case, I didn’t use ALL() and still CALCULATE returns no values with method 3.

    Any insights would greatly be appreciated as I am literally at the end of my rope here, having spent days on this and making no progress.

    Reply

Leave a comment