Measure displaying last week sum

[Anonymous]

What would be the easiest way to create a measure which calculates the sum of a metric for the last week? 

[edhans · Accepted solution]

As long as you have a date table, there are probably 10 different ways to do this. One such way is this function:

Last Week Sales = 
CALCULATE(
    [Total Sales],
    DATESINPERIOD('Calendar'[Date],TODAY(),-7,DAY)
)

If you wanted the last calendar week, then you'd need the week number in your date table (the WEEKNUM() function will do this, or you could generate in Power Query with Date.WeekOfYear([Date]), but then use this function:

Last Calendar Week Sales = 
CALCULATE(
    [Total Sales],
    'Calendar'[Week Number] = WEEKNUM(TODAY(),1) -1
)

[ssathiyan]

Thanks! In my case, I have orders in weeks like in week 3, week 5, week 6, week 8... 

Could you guide me how we can sum the previous week orders in this irregular periods? 

Thanks in advance.

[Anonymous]

Thanks, This is really helpful.

[Anonymous]

HI there @edhans , 

I'm hoping you might be able to figure out why my weekly measures aren't working? 

I'm trying to get 'last week', 'week prior to last' and also YTD functions for average eng rate & total engagement metrics. 

Below is the file link with my measures that are giving me 'blank'

https://www.dropbox.com/s/85v0wkkw4ez7w9d/Example%20file.pbix?dl=0

Thank you so much!

Sonia

[TomMartens]

Hey,

the easiest way is to create a separate date table.

For a start read this post, that describes a lot of common patterns regarding through time in DAX:

https://www.daxpatterns.com/time-patterns/

Regards,

Tom