Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 wha...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    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's avatar
amitchandak
Super User
4 years ago

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