Forum Discussion

FletchNZ's avatar
FletchNZ
Frequent Visitor
8 years ago
Solved

DAX Help - Prior year rolling total

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?

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      FletchNZ
      Frequent Visitor
      8 years ago

      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.