Skip to main content
cancel
Showing results for 
Search instead 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

Reply
sabd80
Helper III
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. 

2024-04-23_15-59-08.png

7 REPLIES 7
AMeyersen
Resolver I
Resolver I

@sabd80 , if you provide some details about what's not working, we might help you further.

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.

2024-04-24_09-43-19.png



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

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

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

To help you further, I'd need some more information

  • 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?
AMeyersen
Resolver I
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
    )

thanks @AMeyersen , that did not work.

Helpful resources

Announcements
Europe Fabric Conference

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.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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