DAX - Help with Measure to find Sales from Last Week or Earlier Week

[bvy]

Hello. I have a very typical data model with a Sales fact table and a Dates table. I have a measure that looks like this: 

SalesLastWeek = CALCULATE([Sales], FILTER(ALL(Dates), Dates[WeekNum] = MAX(Dates[WeekNum]) - 1))

The issue is that there isn't always sales for a particular selection from last week -- in which case I'd like to alter the formula to look at earlier weeks and pick the most recent one that had sales. Imagine for example that the store is closed during Christmas week. Then SalesLastWeek for the first week in January should return sales for the week before Christmas, loosely speaking. Yes, I realize "SalesLastWeek" becomes a misnomer now... 

I know details are sketchy here so feel free to make assumptions. I just need a direction. Thanks. 

[MFelix]

Hi @bvy ,

You need to pick up the last week that has data so you need to do somethig similar to the following:

SalesLastWeek =
VAR MAXWEEKYEAR =
    MAXX (
        TOPN (
            1,
            FILTER (
                ALL ( Dates[WeeKNumYear] ),
                Dates[WeeKNumYear] <= MAX ( Dates[WeeKNumYear] )
            ),
            Dates[WeeKNumYear], DESC
        ),
        Dates[WeeKNumYear]
    )
RETURN
    CALCULATE (
        [Sales],
        FILTER ( ALL ( Dates ), Dates[WeekNumYear] = MAXWEEKYEAR )
    )

I added a WeenunmYear because if you only have week num on the change of year result will be incorrect.