Forum Discussion
DAX Last 4 Weeks from Week Number not Date Calculation
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 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.
6 Replies
- Greg_Deckler
Community Champion
Anonymous Use Sequential and then it is a cake walk: (1) Sequential - Microsoft Power BI Community
- 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."