Card visual with the sales of the previous week

[Jop1235123]

Hi Everyone,

Currently i am busy with making cards to show the sales of the current and previous week. My formula works fine except if there is a year transition. The previous week card shows (blank) instead of the correct value. I am using the following formula to calculate the sales of previous week: 

Sales last week =

VAR Currentweek = WEEKNUM(TODAY(),21)

VAR Currentyear = YEAR(TODAY())

VAR Maxweek = CALCULATE(MAX('Date'[Week]), ALL('Date'))

RETURN

SUMX(FILTER(ALL('Date'), IF(Currentweek=1,

'Date'[Week]=Maxweek && 'Date'[Jaar]=Currentyear -1,

'Date'[Week] = Currentweek -1 && 'Date'[Jaar] = Currentyear)), [Total sales])

Beside this formula i made of course a date table with week and year column. Because currently it is week 7 i changed this part of the formula SUMX(FILTER(ALL('Date'), IF(Currentweek=1, in SUMX(FILTER(ALL('Date'), IF(Currentweek=7 to test if it shows the sales of the last week in the previous year (2023-52 total sales 5000). However it is showing blank and i dont get what goes wrong. 

Hopefully someone has the correct solution, thanks in advance:).

Gr, 

Jop 

[lbendlin]

In your calendar table include a column that clearly indicates the year-week.  Then use the current filter context to find the largest year-week that is smaller than your "current"  one.

[Sahir_Maharaj · Accepted solution]

Hello @Jop1235123,

Can you please try this revised DAX: 

Sales Last Week =
VAR CurrentWeek = WEEKNUM(TODAY(), 21)
VAR CurrentYear = YEAR(TODAY())
VAR PreviousWeek = IF(CurrentWeek = 1, CALCULATE(MAX('Date'[Week]), ALL('Date'), 'Date'[Year] = CurrentYear - 1), CurrentWeek - 1)
VAR PreviousYear = IF(CurrentWeek = 1, CurrentYear - 1, CurrentYear)
RETURN
SUMX(
    FILTER(
        ALL('Date'),
        'Date'[Week] = PreviousWeek && 'Date'[Year] = PreviousYear
    ),
    [Total Sales]
)

[Jop1235123]

Thank u very much Sahir.