Forum Discussion
Measure displaying last week sum
What would be the easiest way to create a measure which calculates the sum of a metric for the last week?
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 )
5 Replies
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 )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
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
