DAX Last 4 Weeks from Week Number not Date Calculation

[Anonymous]

Total Sales Value Last 4 Weeks = 
VAR CurrentWeek = SELECTEDVALUE( Dates[Week_Number] )
VAR CurrentYear = SELECTEDVALUE( Dates[Year] )
VAR MaxWeekNumber = CALCULATE( MAX ( Dates[Week_Number] ), ALL( Dates ))
VAR LastWeekSales = 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] )
VAR Last2WeekSales = SUMX(
                        FILTER( ALL( Dates ),
                            IF( CurrentWeek = 1,
                                Dates[Week_Number] = MaxWeekNumber && Dates[Year] = CurrentYear - 1,
                                Dates[Week_Number] = CurrentWeek - 2 && Dates[Year] = CurrentYear )),
                            [Total Sales Value] )
VAR Last3WeekSales = SUMX(
                        FILTER( ALL( Dates ),
                            IF( CurrentWeek = 1,
                                Dates[Week_Number] = MaxWeekNumber && Dates[Year] = CurrentYear - 1,
                                Dates[Week_Number] = CurrentWeek - 3 && Dates[Year] = CurrentYear )),
                            [Total Sales Value] )

RETURN
[Total Sales Value] + LastWeekSales + Last2WeekSales + Last3WeekSales 

Hi All

I am using the above DAX to calculate the last 4 weeks Sales off Week and Year fields in my date table
Any pointers on how this can be simplified, doesn't feel like this can possibly be the most efficient way!

I want to also write this for the last 12 weeks, so hoping for a more straightforward way to write it!

Any help appreciated!

[Anonymous]

How would that incorporate into my scenario?

[Greg_Deckler]

@Anonymous Create a Sequential column using the provided DAX. Then the last 4 week is always just -4 and last twelve are -12. You don't have to worry about years because the week number is sequential across years.

"Suppose you have a list of years and weeks in those years (or months). Often, it would be nice to have a sequential number for this kind of data in order to make certain math calculations easier for comparisons between discrete units of time, etc."

[Anonymous]

I'm not with you

I am trying calculate the total of the last 4 weeks and the total of the last 12 weeks, unsure how sequential helps here?

[Greg_Deckler · Accepted solution]

@Anonymous If you have a Sequential week column then you do this:

Total Sales Value Last 4 Weeks = 
VAR __CurrentWeek = MAX( Dates[Sequential] )
VAR __CurrentMinus4 = __CurrentWeek - 4
RETURN
SUMX(FILTER(ALL(Dates), [Sequential] <= __CurrentWeek && [Sequential] >= __CurrentMinus4),[Total Sales Value])

Seems self explanatory.

[Anonymous]

Thanks a lot!

[Greg_Deckler]

@Anonymous Use Sequential and then it is a cake walk: (1) Sequential - Microsoft Power BI Community