cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
luketerry
Advocate II
Advocate II

How to Calculate monthly forecast based on selected month view

I have two sets of values: actual sales for the month and a monthly forecast snapshot that has a new version every month.

How do you update a table that shows the forecast based on which dynamic forecast month version they’d like to see?

 

In Power BI I have 4 tables, a date table, a sales table, a forecast table, and a DF month selection table. The date table has a one to many relationship to both the sales and forecast table. The DF month selection table has no relationship to the other tables so that it doesn’t restrict which dates will appear from the date table.

 

I really struggle with replicating pretty straight forward excel calculations in Power BI.

Here’s the excel formula. I’m trying to calculate column D in Power BI.

 

Thanks for your help

test.png

1 ACCEPTED SOLUTION
Chas2002
Helper III
Helper III

Hi Luke,

 

The DAX would look something like:  IF([Selected DF Month]>=[Month Num], [Actual Sales]=[Forecast])

 

I haven't had a chance to test, but that would close to your existing excel formula.

 

Chas

 

View solution in original post

6 REPLIES 6
Chas2002
Helper III
Helper III

Hi Luke,

 

The DAX would look something like:  IF([Selected DF Month]>=[Month Num], [Actual Sales]=[Forecast])

 

I haven't had a chance to test, but that would close to your existing excel formula.

 

Chas

 

View solution in original post

Thanks Chas.  This is what I initially tried.  However there's an odd quirck.

It works when I select the DF month to be between Jan-June.  However, once I get to July it starts pulling in the blank CY (current year actuals) values instead of the DF (see below).

 

I think the reason for this is it's looking at the average of the months in the table overall (which would be 6), so any months above 6 and below the select month pull CY.

 

I'm not sure how to get around this.

 

test.png

Hi Luke,

 

It would take a while to replicate the four tables and relationships.

 

Can you place your .pbix on dropbox or similar.  Santize the data.

 

Thanks,

Chas

Hi Chas,

It was a pretty massive report so after making a copy, stripping down the data, and resetting the tables to send to you as an example, it looks like it's no longer a problem and it calculates as I would expect.

 

Unforuntately, now I have to figure out why it is a problem in my existing report as I can't rebuild everything from scratch.

 

Thanks for your help

Just add an IF([CY] to check to see if CY existits and then perform an alternative calculation if it does not.  You dont need to say ISBLANK you can just say IF([Measure],then,else).  If you leave off the else term it will return NULL  measure if blank.  This is also a useful trick if you want to block a measure from calcualating for display purposes.. 

 

 

Hi Seward,

Please see my response here for why the if statement is causing an issue.

 

Thanks

 

https://community.powerbi.com/t5/Desktop/How-to-Calculate-monthly-forecast-based-on-selected-month-v...

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.