Getting previous weeks results while displaying two dimensions

[fafhrd]

Sample file 

I have a list of revenue forecasts for event revenue which are generated each week. I want to display the forecast revenue with the date of the forecast, how many weeks to go until the month of the events and the previous weeks forecast so I can eventually have a measure calculating the change in the forecast each week.

The measure I have created works fine if only the forecast date is in the table, but fails as soon as I add the "Weeks out" column (spinning wheel never stops spinning).

Previous Wk forecast = 
VAR _CurrentDate = MAX('Forecast Date'[Date])
VAR _PreviousWk = 
    CALCULATE (
        LASTDATE('Forecast Date'[Date]),
        FILTER ( ALL('Forecast Date'[Date]), 'Forecast Date'[Date] < _CurrentDate )
    )
RETURN
CALCULATE (
    Forecasts[Forecast Revenue],
    FILTER ( ALL('Forecast Date'[Date]), 'Forecast Date'[Date] = _PreviousWk )
)

Model is pretty standard, fact table of forecasts and 2 date dimension tables (one for forecast date and the other for event month) linked via the appropriate date column in forecasts.

Forecasts Table

AGG_WEEK	Event status	Revenue type	Revenue	Event month	Forecast date	Weeks out
20210505	13	Sponsorship	0	1/11/2022	5/05/2021 0:00	77
20210505	13	Sponsorship	0	1/12/2022	5/05/2021 0:00	82
20210505	13	Sponsorship	0	1/02/2023	5/05/2021 0:00	91
20210505	13	Sponsorship	0	1/06/2023	5/05/2021 0:00	108
20210505	13	Sponsorship	0	1/08/2023	5/05/2021 0:00	116
20210512	13	Sponsorship	0	1/06/2021	12/05/2021 0:00	2
20210512	13	Sponsorship	0	1/07/2021	12/05/2021 0:00	7
20210512	13	Sponsorship	0	1/08/2021	12/05/2021 0:00	11
20210512	13	Sponsorship	0	1/04/2024	12/05/2021 0:00	150
20210512	13	Sponsorship	0	1/08/2024	12/05/2021 0:00	168
20210512	13	Sponsorship	0	1/12/2024	12/05/2021 0:00	185
20210512	13	Sponsorship	0	1/08/2025	12/05/2021 0:00	220
20210519	13	Sponsorship	0	1/06/2021	19/05/2021 0:00	1
20210519	13	Sponsorship	0	1/07/2021	19/05/2021 0:00	6
20210519	13	Sponsorship	0	1/08/2021	19/05/2021 0:00	10
20210519	13	Sponsorship	0	1/04/2022	19/05/2021 0:00	45

Event Date

DATE_ID	Date	YEAR_FY	Month_Desc
20210701	1/07/2021	2022	July
20210901	1/09/2021	2022	September
20211101	1/11/2021	2022	November
20220601	1/06/2022	2022	June
20211201	1/12/2021	2022	December
20220301	1/03/2022	2022	March
20211001	1/10/2021	2022	October
20220401	1/04/2022	2022	April
20220201	1/02/2022	2022	February
20220101	1/01/2022	2022	January
20210801	1/08/2021	2022	August
20220501	1/05/2022	2022	May

Forecast Date

DATE_ID	Date	YEAR_FY
20210531	31/05/2021	2021
20210530	30/05/2021	2021
20210529	29/05/2021	2021
20210528	28/05/2021	2021
20210527	27/05/2021	2021
20210526	26/05/2021	2021
20210525	25/05/2021	2021
20210524	24/05/2021	2021
20210523	23/05/2021	2021
20210522	22/05/2021	2021
20210521	21/05/2021	2021
20210520	20/05/2021	2021
20210519	19/05/2021	2021
20210518	18/05/2021	2021
20210517	17/05/2021	2021
20210516	16/05/2021	2021
20210515	15/05/2021	2021
20210514	14/05/2021	2021
20210513	13/05/2021	2021
20210512	12/05/2021	2021
20210511	11/05/2021	2021
20210510	10/05/2021	2021
20210509	9/05/2021	2021
20210508	8/05/2021	2021
20210507	7/05/2021	2021
20210506	6/05/2021	2021
20210505	5/05/2021	2021
20210504	4/05/2021	2021
20210503	3/05/2021	2021
20210502	2/05/2021	2021
20210501	1/05/2021	2021

[amitchandak]

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

[fafhrd]

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.

[amitchandak]

@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.

[fafhrd]

Hi Amit,

Link to sample PBIX is at the top of my original post. Here it is again.

Original post also shows sample data from the 3 tables in the model. 1 fact table and 2 date dimension tables.