Forum Discussion
Getting previous weeks results while displaying two dimensions
fafhrd , usually for last week we use date table and week rank
new column in date table(Join date table with both facts)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format
measures example for this week and last week
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))
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
Hi Amit,
Thank you for your feedback. I tried your solution and same issue as my code, it stops working when I add the "Weeks out" dimension to the table.
Incidentally, I WAS using week rank in my code before. I switched to the technique I posted because I can't guarantee there will be a forecast posted each week. In which case I need to retrieve and compare to the last available forecast.
But let's leave that aside for the moment. The most pressing issue is to get the previous week forecast measure working so that I can display BOTH the forecast date and the Weeks Out dimension from the Forecasts facts table.
The purpose of "weeks out" is to show how many weeks the sales team has to convert the forecast revenue (tentative bookings) into actual revenue (confirmed bookings). So in the screenshot, I have selected to see forecasts for October 2022. As at the forecast made on 26/5/2021, there are 70 weeks to go until October 2022 and so and so forth.
fafhrd , are you using FY, Month, Forecast Date and Week out from Date table ?
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.