topic Week on Week Actual Variance + with Forecast Variance in DAX Commands and Tips

Week on Week Actual Variance + with Forecast Variance

pupadhya — Fri, 18 Mar 2022 13:55:59 GMT

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

Re: Week on Week Actual Variance + with Forecast Variance

amitchandak — Sat, 19 Mar 2022 04:25:49 GMT

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)

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-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8

Re: Week on Week Actual Variance + with Forecast Variance

v-xiaotang — Tue, 22 Mar 2022 07:48:07 GMT

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.

Re: Week on Week Actual Variance + with Forecast Variance

PBIdashboards — Wed, 24 Jun 2026 03:35:03 GMT

The issue is that using Week as a text/number column without a proper Date table means DATEADD and PREVIOUSWEEK don't work. The fix is to use FILTER + ALL against the Week column directly:

Prev Week Revenue =
VAR _currentWeek = MAX(Table[Week])
RETURN
CALCULATE(
SUM(Table[Revenue]),
ALL(Table[Week]),
Table[Week] = _currentWeek - 1
)

WoW Variance =
DIVIDE([Revenue] - [Prev Week Revenue], ABS([Prev Week Revenue]))

This bypasses the Date table requirement entirely works directly with your Week number column.

For teams where weekly reporting spans multiple metrics (revenue + units + margin), each metric needs this pattern duplicated. Flexa Tables on AppSource handles WoW as a built-in column select the two weeks to compare directly in the published report