Calculating Weekly Consumption on Visual Table

[Anonymous]

Hi, 

This might be a super simple fix and i am just dumb but I want to create a new measure that would give me the difference between the Totals between 2 days. For example, I would like to know what the weekly consumption was between November 7th and November 14th (the difference between the total of November 14th and November 7th) and then November 21st and November 14th, and so on. Any help to come up with something similar to this would be greatly appreciated. 

Thanks, 

[v-jingzhang · Accepted solution]

Hi @Anonymous 

It would be better if you could provide some sample data. Assume your data is like below and that you have a date table connected to fact table, you can create below measure to get the weekly total difference. 

Weekly Diff = 
var _thisWeekTotal = SUM('Table'[Sales])
var _previousWeekTotal = CALCULATE(SUM('Table'[Sales]),DATEADD('Date'[Date],-7,DAY))
return
_thisWeekTotal-_previousWeekTotal

Result

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[amitchandak]

@Anonymous , one way is using date table a week behind measures

7 behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-7,DAy))

another is using these column in date table

new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

and then create  WOW measures
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8