DAX Help - Prior year rolling total

[FletchNZ]

Hey all,

I'm stuck trying to get a prior year 12 month rolling total dax Query working.

So first I have a 12 month rolling total which works great

12M Total Service Request:= 

CALCULATE (
    [Total Service Request],
    DATESBETWEEN (
        'Date'[Date],
   NEXTDAY (   SAMEPERIODLASTYEAR (  LASTDATE ( 'Date'[Date] ) ) ),
        LASTDATE ( 'Date'[Date] )
    
)
)

and for the Prior year I use the below, however for the very first date for this formula it returns the total for everything because the "samePeriodLastYear" is referencing a date that doesnt exist. I've tried a vew things but i've found nothing that works. 

Prior 12M Total Service Request:=  Calculate('Service Request'[12M Rolling Total Service Request], SAMEPERIODLASTYEAR('Date'[Date]))

Anyone have any recomendations on how to resolve this?

[Anonymous · Accepted solution]

HI @FletchNZ,

I think it may related to filter conflict between different time intelligence functions.

Time Intelligence in Power BI Desktop

Optimizing DAX expressions involving multiple measures

If this is a case, I'd like to suggest you manually point out date range which dax formula calculate instead to nested multiple measures with specific filters.

Sample:

12M Total Service Request :=
VAR currDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        [Total Service Request],
        FILTER (
            ALLSELECTED ( 'Date' ),
            [Date]
                >= DATE ( YEAR ( currDate )-1, MONTH ( currDate ), DAY ( currDate ) )
                && [Date] <= currDate
        )
    )

Prev 12M Total Service Request :=
VAR currDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        [Total Service Request],
        FILTER (
            ALLSELECTED ( 'Date' ),
            [Date]
                >= DATE ( YEAR ( currDate ) - 2, MONTH ( currDate ), DAY ( currDate ) )
                && [Date]
                    <= DATE ( YEAR ( currDate ) - 1, MONTH ( currDate ), DAY ( currDate ) )
        )
    )

Regards,

Xiaoxin Sheng

[FletchNZ]

Thanks for that it worked a charm!

I end up having to move from a Dax generated date table to a M date generated table and that along with your formulas it all worked perfect. No idea why I was getting weird results from the www.SQLbi.com Dax date code.