﻿ 5 ways to Calculate Last 12 Months in DAX for Power BI - BI Gorilla
Written By Rick de Groot

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

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

## 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 months 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’s 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)

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

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

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

Share on:

Power Query

Power Query

Power Query

Power Query

Power BI

Power BI

## Best Ways to Share Reports in Power BI

1. How can I calculate both last 12 months and 6 months in dax?

• You can use any of the methods above. For the 6 months calculation, just swap the 12 to 6.

2. Hi Rick,

Thank you so much for this! Was batlling creating a correct measure for my YTD values and this has worked perfectly, am so grateful!!

God bless you for sharing your knowledhe with the world.

Anthony

• Thanks Anthony ! 🙌🏻

3. Hello
I am trying to use version 4 to give me last 12 months but keep getting 13 months in Feb. I am using a 454 calendar with fiscal year end being Jan. When using above measure I get last 13 months when I pull Feb. Any suggestions.

• I was able to figure it out. ☻ I replaced the following line as follows

VAR MinDate = EDATE( MaxDate, -12 ) — move it back 12 months

VAR MinDate = MaxDate-364 — move it back 364 days

Since my calendar is based on 364 days (4-5-4) with fiscal year ending Jan.

• Awesome Joe. Glad that worked. If you want to be 100% sure things work in your 4-4-5 calendar, I would recommend creating an index number that increments with each change in the periods. A period is 4 or 5 weeks depending on the part of the year you are in. When you base your calculation on an index number like that, you will be 100% sure you pick the right period. You can then choose to filter the dates that are between the MAX( IndexMonth) and the MAX(IndexMonth) -12.

Happy coding!

Rick

4. I found a solution. It’s impossible to do with filters applied to DimDate[Date] or DATESINPERIOD(). Instead, one must resort to a slicer selecting a different measure for each Day Type. Then it works.

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