Forum Discussion

Miguelcrz's avatar
Miguelcrz
Helper I
6 years ago

WEEK VARIATION

Hello,   I need to have the variation % of my sales comparing it vs the last week like this:       My power BI currently looks like this:   And my table of time looks like this (S...
Need Help
amitchandak's avatar
amitchandak
Super User
6 years ago

Miguelcrz , first of all have a separate week table. Should include the year in that.  The best way is to use week year rank , Please follow my blog for that.

With week no. The date can Week -year/week table

This Week = CALCULATE(sum('order'[sales]), FILTER(ALL('Date'),'Date'[Week]=max('Date'[Week])))
Last Week = CALCULATE(sum('order'[sales]), FILTER(ALL('Date'),'Date'[Week]=max('Date'[Week])-1))

 

With Rank

This Week = CALCULATE(sum('order'[sales]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[sales]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

 

refer these to see how rank works

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