Forecast Mix of Result & Plan

[Mohsin_Hassan]

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 🙂

[karnold · Accepted solution]

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

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!

[Mohsin_Hassan]

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?

[Mohsin_Hassan]

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

[Mohsin_Hassan]

This worked perfectly, thanks.

[karnold]

Hi

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

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!

[Mohsin_Hassan]

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?