DAX Custom Week Calculations

[Anonymous]

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

[Anonymous]

Thank you!

[v-cazheng-msft · Accepted solution]

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