Calculate previous/ last week sales with a custom calendar

DAX DAX Calculations
21

Hi @pedroccamara,

I’ve added a WeeknYear column to the Dates table. Logic for that column is:
[Year] * 10000 + [Week Number] * 100, type number
So this will work with Custom calendars and your Weekno requirement. Last week sales then becomes:

Sales LW (WeeknYear) = 
VAR myWeekOffset = SELECTEDVALUE( Dates[WeeknYear] ) 
VAR PrevWeek = CALCULATE( MAX( Dates[WeeknYear] ), FILTER( ALL( Dates ), Dates[WeeknYear] < myWeekOffset ))
VAR ListWeeks = SELECTCOLUMNS( ADDCOLUMNS( VALUES( Dates[WeeknYear]), "LW", CALCULATE( MAX( Dates[WeeknYear] ), FILTER( ALL( Dates ), Dates[WeeknYear] < EARLIER( Dates[WeeknYear])))), "Weeks", [LW] )
RETURN

COALESCE(
    CALCULATE( [Total Sales],
      FILTER( ALL( Dates ),
      Dates[WeeknYear] = PrevWeek )
    ),
    CALCULATE( [Total Sales],
        FILTER( ALL( Dates ),
            Dates[WeeknYear] IN ListWeeks
        )
    )
)

With this result.
image

Here’s my updated test file. eDNA - LW Sales.pbix (451.0 KB)
I hope this is helpful.

2 Likes
22

Awesome Melissa, thank you so much!!!