DAX Rolling forward 36 month

[sabd80]

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. 

[AMeyersen]

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

[sabd80]

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.

[AMeyersen]

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

[sabd80]

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

[AMeyersen]

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]

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
    )

[sabd80]

thanks @AMeyersen , that did not work.