Forum Discussion
Calculating Weekly Consumption on Visual Table
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,
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-_previousWeekTotalResult
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
2 Replies
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 formatand 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=pnAesWxYgJ8Hi 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-_previousWeekTotalResult
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
