cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## DAX Rolling forward 36 month

Hi,
I have below data, I would like to create a measure for rolling 36 month total, based on the selected year.
for example if the selected year is 2024, the minimum month for that year is 01/01/2024, the rolling 36 month shoud sum up from 01/01/2024 until 31/12/2025.

I don't have a date table.

7 REPLIES 7
Resolver I

Helper III

HI @AMeyersen ,

In the screenshot below, the column labeled 'Your measure' contains the DAX formula you supplied, which is based on year-end data. However, I require a rolling 36-month calculation instead. For instance, if the first date is 1/05/2022, I need the total to include data up to and including 1/04/2025, spanning a rolling 36-month period.

Additionally, I have a year slicer, and I want the figures to remain consistent when I select a specific year.

Resolver I

Hi @sabd80 , thank you for the clarification.
Does this work for you?

``````yourmeasure =
-- gets the first of the minimum selected month in the current filter context
VAR _startDate =
EOMONTH ( CALCULATE ( MIN ( 'Table'[Date] ) ), -1 ) + 1
VAR _endDate =
EDATE ( _startDate, 36 ) - 1
-- if you want to include the first of month at the end of the period just remove "-1" at the end
RETURN
CALCULATE (
SUM ( 'Table'[Total] ),
'Table'[Date] >= _startDate
&& 'Table'[Date] <= _endDate
-- if the year slicer filters a separate column, you need to add:
-- ,REMOVEFILTERS ( 'Table'[Year] )
)``````
Helper III

This is still showing the figures for the filtered Year. it looks like the REMOVEFLITERS is not working

Resolver I

Hi @sabd80 ,

sorry to hear the suggested solution does not work (yet).
I am pretty sure you'll get a working formula if you get the REMOVEFILTERS part right.

If you are not sure how to do it, you can use ALLEXCEPT() instead of REMOVEFILTERS(). Include all columns in ALLEXCEPT() which need to remain filtered (in general columns you use to group your results

• are there any more relevant columns or tables involved in your data model (e.g. used in slicers or filters, related tables which might be filtered, ...)?
• do you use an auto-created date hierarchy on 'Table'[Date]?
• would you be able to share an example *.pbix file?
Resolver I

I'd try something like this:

``````yourmeasure =
VAR _startYear =
CALCULATE ( YEAR ( MIN ( 'Table'[Date] ) ) )
VAR _end_year = _startYear + 2
VAR _startDate =
DATE ( _startYear, 1, 1 )
VAR _endDate =
DATE ( _end_year, 12, 31 )
RETURN
CALCULATE (
SUM ( 'Table'[Total] ),
'Table'[Date] >= _startDate
&& 'Table'[Date] <= _endDate
)``````
Helper III

thanks @AMeyersen , that did not work.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors