Forum Discussion
Calculating the last 2 closed/completed weeks.
Hi all
I would love to have some help dynically cacluating Sales vs. forecast for completed weeks.
Let's say I have a graph showing sales vs. forecast. Today is in the middle of week 42
I need help calculating three scorecards showing the following:
Last completed week (41) vs Week (41) Forecast
Previous two weeks (40,39) vs week (40,39) Forecast.
and more.
basically I need help filtereing on the last or more completed week, and as soon as we are in week 43, then it changes to
Week 42 vs week 42 forecast
Week (41,40) vs week (41,40) forecast.
Any help would be appreciated
Br
Anonymous , Typically I create a week Rank/ Week num can be used. On year week or week start date in Date/week table and try like
New columns
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
measures
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
Last 2 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-2 && 'Date'[Week Rank]<=max('Date'[Week Rank])))Last 2 weeks before Last 1= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-3 && '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
1 Reply
- amitchandak
Super User
Anonymous , Typically I create a week Rank/ Week num can be used. On year week or week start date in Date/week table and try like
New columns
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
measures
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
Last 2 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-2 && 'Date'[Week Rank]<=max('Date'[Week Rank])))Last 2 weeks before Last 1= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-3 && '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