Forum Discussion

fafhrd's avatar
fafhrd
Frequent Visitor
5 years ago

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 t...
DAX
Time Intelligence
fafhrd's avatar
fafhrd
Frequent Visitor
5 years ago

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's avatar
amitchandak
Super User
5 years ago

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's avatar
    fafhrd
    Frequent Visitor
    5 years ago

    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.