Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
fafhrd
Frequent Visitor

Getting previous weeks results while displaying two dimensions

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_WEEKEvent statusRevenue typeRevenueEvent monthForecast dateWeeks out
2021050513Sponsorship01/11/20225/05/2021 0:0077
2021050513Sponsorship01/12/20225/05/2021 0:0082
2021050513Sponsorship01/02/20235/05/2021 0:0091
2021050513Sponsorship01/06/20235/05/2021 0:00108
2021050513Sponsorship01/08/20235/05/2021 0:00116
2021051213Sponsorship01/06/202112/05/2021 0:002
2021051213Sponsorship01/07/202112/05/2021 0:007
2021051213Sponsorship01/08/202112/05/2021 0:0011
2021051213Sponsorship01/04/202412/05/2021 0:00150
2021051213Sponsorship01/08/202412/05/2021 0:00168
2021051213Sponsorship01/12/202412/05/2021 0:00185
2021051213Sponsorship01/08/202512/05/2021 0:00220
2021051913Sponsorship01/06/202119/05/2021 0:001
2021051913Sponsorship01/07/202119/05/2021 0:006
2021051913Sponsorship01/08/202119/05/2021 0:0010
2021051913Sponsorship01/04/202219/05/2021 0:0045

 

Event Date

 

DATE_IDDateYEAR_FYMonth_Desc
202107011/07/20212022July
202109011/09/20212022September
202111011/11/20212022November
202206011/06/20222022June
202112011/12/20212022December
202203011/03/20222022March
202110011/10/20212022October
202204011/04/20222022April
202202011/02/20222022February
202201011/01/20222022January
202108011/08/20212022August
202205011/05/20222022May

 

Forecast Date

 

DATE_IDDateYEAR_FY
2021053131/05/20212021
2021053030/05/20212021
2021052929/05/20212021
2021052828/05/20212021
2021052727/05/20212021
2021052626/05/20212021
2021052525/05/20212021
2021052424/05/20212021
2021052323/05/20212021
2021052222/05/20212021
2021052121/05/20212021
2021052020/05/20212021
2021051919/05/20212021
2021051818/05/20212021
2021051717/05/20212021
2021051616/05/20212021
2021051515/05/20212021
2021051414/05/20212021
2021051313/05/20212021
2021051212/05/20212021
2021051111/05/20212021
2021051010/05/20212021
202105099/05/20212021
202105088/05/20212021
202105077/05/20212021
202105066/05/20212021
202105055/05/20212021
202105044/05/20212021
202105033/05/20212021
202105022/05/20212021
202105011/05/20212021
4 REPLIES 4
amitchandak
Super User
Super User

@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

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.

2021-05-27_14-24-00.png

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.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors