topic Re: Forecast Mix of Result & Plan in DAX Commands and Tips

Forecast Mix of Result & Plan

Mohsin_Hassan — Mon, 09 May 2022 14:21:02 GMT

Hello,

I am an absolute noob at this so excuse me if my question is stupid.

I want to solve a problem that I have,

1) Begining of the year we plan sales for the whole year month by month we call that BP (Business Plan) [BP Table]

2) Then every month I get forecast for the next 6 month from sales team, and we call that plan (P) and number them P01 (Plan for Jan) P02 (Plan for Feb) and so forth. [Forecast Table]

3) Lastly as the months go by I get Result [Result Table]

How do show a rolling forecast where some months are result and some are Forecast.

Ideally I would like the below table but the P05 22 Apr have Result followed by forecast from that Plan.

I hope im making sense.

Thanks 🙂

Re: Forecast Mix of Result & Plan

karnold — Mon, 09 May 2022 16:23:04 GMT

I would start by transforming the data in the 3 tables to a star schema, rather than having them seperate, like this:

Here is the code for the 2 measure in the Sales Summary table:

Sales = SUM('Sales Summary'[Sales Amount])

Plan or Results Sales = VAR _ResultSales = CALCULATE([Sales],REMOVEFILTERS('Plan'),'Plan'[Plan Name]="Result") VAR _Sales = [Sales] return IF(_Sales>0,_Sales,_ResultSales)

Here are 3 matrix visuals showing the results:

I placed the pbix file on my GitHub at https://github.com/kevarnold972/blogshare/blob/master/Community/Forecast%20Mix%20of%20Result%20%26%20Plan.pbix

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

Re: Forecast Mix of Result & Plan

Mohsin_Hassan — Tue, 10 May 2022 08:08:11 GMT

Hi,

Thank you for all the work,

There is an issue though, the total in P05 22 is not correct, after adding the result to P05 22 Forecast the total should be $6,234,233 but its $4,890,004 (with out the Result added)

Any idea?

Re: Forecast Mix of Result & Plan

karnold — Tue, 10 May 2022 23:36:50 GMT

Good catch. Here is the measure that will produce the proper total by the MonthShort column.

Plan or Results Sales with Total = VAR _TotalbyMonth = ADDCOLUMNS( VALUES( 'Calendar'[MonthShort] ), "@Sales", [Plan or Results Sales] ) RETURN SUMX( _TotalbyMonth, [@Sales] )

When I use that in the last visual I get the better results

Re: Forecast Mix of Result & Plan

Mohsin_Hassan — Wed, 11 May 2022 06:43:42 GMT

This worked perfectly, thanks.

Re: Forecast Mix of Result & Plan

Mohsin_Hassan — Wed, 11 May 2022 07:19:11 GMT

Im sorry if you dont mind can you please explain the logic behind this? This is so i can learn 🙂

Re: Forecast Mix of Result & Plan

Mohsin_Hassan — Mon, 06 Jun 2022 11:02:28 GMT

Hi I found another issue which I need help to address,

If in anymonth BP = 0 then result is calculated as BP,

That as you can understand will show incorrect numbers.

I have a picture below,

BP Number should be this

But it shows as

The Result showing in P06 is corrent but result showing as BP is not the corrent behaviour.

I though i could just modify the exisiting DAX mesaure to filter out BP before calcuating but that didn't seem to work.

Below is what i tried.

Plan or Results Sales = VAR _ResultSales = CALCULATE([Sales], FILTER(ALL(dimPlan), [Plan] = "Result")) VAR _Sales = CALCULATE([Sales], FILTER(ALL(dimPlan), [Plan] <> "BP")) RETURN IF(_Sales>0,_Sales,_ResultSales)

Any idea how I would fix that?