cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bvy
Helper III
Helper III

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

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. 
1 REPLY 1
MFelix
Super User
Super User

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.