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

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:

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

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:

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

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

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

## Recommended Reading…

### Power Query Foundations

**Beginners Guide to Power Query**

**List Functions (200+ Examples)**

**Text Functions (150+ Examples)**

**Creating Tables (40+ Examples)**

**Generate the Ultimate Date Table**

### Advanced Topics

**Master List.Generate with Easy Examples**

**Deep Dive into List.Accumulate**

Dissecting Monsters Could Giant Beasts ACTUALLY Exist?The SCIENCE of… Dauntless

Hi Rick,

I had a question regarding the visual aspect of the data,I have a data range using 21 – 23, I don’t want to show anything that does not have a full 12 months of rolling totals (’21 and currently any ’23 data that has less than 12 months totals). What dax would I need to add to the formula to be able to ‘hide’ it

Hi Rick,

Thanks for this. I am manage to use the DATESBETWEEN to have my 12 months rolling.

However I am trying to have the same one for the previous period. I have try to add SAMEDATELASTYEAR in the formula but not able to succeed. Do you have how I can have DATESBETWEEN Previous Year

Best

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.

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 ! 🙌🏻

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

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.

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:

[dax]

FILTER(

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

DimDate[Date]

)

[/dax]

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.