Dax Actual Profit & Loss v Monthly Forecast Variance

DAX DAX Calculations
1

Hi,

I have a file that contains current year actuals and current year forecasts.

At the end of each month finance then reforecasts for the rest of the year.
The p&l forecast contains two date columns - the date in which it is forecast to happen (Date) and the month end in which we have forecast (Forecast date).

We would like to be able to keep all of the prior months forecast as we would like to see at the end of each month if our forecasting is improving…

I am really stuck on this measure and how to go about this so i have tried to attach a dummy file that i have created to mimic the file and outcome i am trying to achieve.
Data Example.pbix (834.9 KB)

2

Hi @Suzie, please check this post if it applies to your situation and desired outcome.

Hope it helps.

Calculate values prior to the current month - DAX Calculations - Enterprise DNA Forum

3

Hi @Suzie - If needed to keep track of previous months forecast, then can you use Matrix visual as shown in Page 1 of attached PBIX.

Data Example.pbix (117.7 KB)

Thanks
Ankit J

4

@ankit @Harsh

Thanks for providing some solutions. The problem I am facing through is that I need a dax measure that will pick up (current month -1) from the forecast column to correctly calculate what the values for the forecast will be.
There are two dates within the forecast table Date & Forecast Date. The date is linked to the date table where as the forecast Date represents the date for the month in which the forecast was completed for comparison to actuals.

image

5

Hi @Suzie! We noticed that your inquiry was left unsolved for quite some time now.

Looks like your inquiry was out of the experts and users’ bounds.

We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!