Forum Discussion

Da_clint's avatar
Da_clint
Frequent Visitor
3 years ago

DAX rolling 12 month

Hi

 

I have used this DAX to calculate a rolling 12 month spend.

And have used it in a visual which can be filtered by month and year, it works fine when I choose month and year, but when I haven't any selections made it calculates all the way up to deceber 2023 when I only have spend data up to feb 2023.

Is there any way to restirict it so it don't calculate rolling for all of 2023?

 

Spend R12 =
VAR ReferenceDate = MAX (Date_Table[Date])
VAR PreviousDates =
    DATESINPERIOD('Previous Date'[Date],
    ReferenceDate,
    -12,MONTH)
VAR Result =
    CALCULATE(
        [Total Spend],
        REMOVEFILTERS(Date_Table),
        KEEPFILTERS(PreviousDates),
        USERELATIONSHIP(Date_Table[Date], 'Previous Date'[Date]))
RETURN
    Result
Need Help

2 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    You can compare the reference date with the max date from your fact table

    Spend R12 =
VAR ReferenceDate =
    MAX ( Date_Table[Date] )
VAR LastDateEver =
    CALCULATE ( EOMONTH ( MAX ( 'Fact table'[Date] ), 0 ), REMOVEFILTERS () )
RETURN
    IF (
        ReferenceDate <= LastDateEver,
        VAR PreviousDates =
            DATESINPERIOD ( 'Previous Date'[Date], ReferenceDate, -12, MONTH )
        VAR Result =
            CALCULATE (
                [Total Spend],
                REMOVEFILTERS ( Date_Table ),
                KEEPFILTERS ( PreviousDates ),
                USERELATIONSHIP ( Date_Table[Date], 'Previous Date'[Date] )
            )
        RETURN
            Result
    )