Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX Custom Week Calculations

VAR CurrentWeek = SELECTEDVALUE( Dates[Week_Number] ) VAR CurrentYear = SELECTEDVALUE( Dates[Year] ) VAR MaxWeekNumber = CALCULATE( MAX ( Dates[Week_Number] ), ALL( Dates )) RETURN SUMX( FILTER( ALL( Dates ), IF( CurrentWeek = 1, Dates[Week_Number] = MaxWeekNumber && Dates[Year] = CurrentYear - 1, Dates[Week_Number] = CurrentWeek -1 && Dates[Year] = CurrentYear )), [Total Sales Value] )

 

Hi All, 

 

I found the above code to calculate Total Sales for the Previous Week

I want to amend this to calculate Total Sales for the current week last year

 

Can't figure this out, any ideas!

My data is at a week granularity with a field for week and field for year, no date field exists

 

Thanks in advance

DAX
Help appreciated
  • v-cazheng-msft's avatar
    v-cazheng-msft
    4 years ago

    Hi Anonymous,

     

    You may try this Measure.

    Current Week Of last Year =
VAR CurrentWeek =
    SELECTEDVALUE ( Dates[Week_Number] )
VAR CurrentYear =
    SELECTEDVALUE ( Dates[Year] )
VAR PrevYear = CurrentYear - 1
RETURN
    SUMX (
        FILTER (
            ALL ( Dates ),
            Dates[Year] = PrevYear
                && Dates[Week_Number] = CurrentWeek
        ),
        [Total Sales Value]
)

     

    The result looks like this.

     

     

    Also, attached the pbix file as reference.

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

     

    Best Regards,

    Community Support Team _ Caiyun

2 Replies

  • v-cazheng-msft's avatar
    v-cazheng-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous,

     

    You may try this Measure.

    Current Week Of last Year =
VAR CurrentWeek =
    SELECTEDVALUE ( Dates[Week_Number] )
VAR CurrentYear =
    SELECTEDVALUE ( Dates[Year] )
VAR PrevYear = CurrentYear - 1
RETURN
    SUMX (
        FILTER (
            ALL ( Dates ),
            Dates[Year] = PrevYear
                && Dates[Week_Number] = CurrentWeek
        ),
        [Total Sales Value]
)

     

    The result looks like this.

     

     

    Also, attached the pbix file as reference.

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

     

    Best Regards,

    Community Support Team _ Caiyun