Week on Week Actual Variance + with Forecast Variance

[pupadhya]

Week variance

Hello All,

I have data which gets updated on weekly basis, So source file has Week column under that every week data gets updated. For Ex: 07 March week(Previous Week) will have some customer revenue and 14 March week (Current Week) will have updated revenue details and these two details under same column under the header of Week, so how do I find the variance between two weeks?

When i try to go for measure, it is not recognizing any table so that I can find the variance and additionally once I have the variance for the above two, I need to find the variance between Current Week vs Forecasted revenue for the month (which is constant figure).

Also, in the picture Week 7 & 14 is coming in the same column, how we can show week 7 & 14 in seperate column?

Please let me know how I can approach this? Maybe i can share file. Thanks 

[v-xiaotang]

Hi @pupadhya 

I just want to confirm if you resolved this issue? If yes, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

If you need more help, please let me know.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@pupadhya , Create a new table with distinct of this column( If there is year take that)

Distinct(Table[Day])

Or have table with week year

Create a rank of Day oe week year in new table , new column

Week Rank = RANKX(all('Date'),'Date'[Day],,ASC,Dense)

or

Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

then try measures  like
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-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8