How to Calculate monthly forecast based on selected month view

[Anonymous]

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

[Chas2002 · Accepted solution]

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

[Anonymous]

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.

[Chas2002]

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

[Anonymous]

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

[Seward12533]

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

[Anonymous]

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