cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mohsin_Hassan
Frequent Visitor

Forecast Mix of Result & Plan

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. 

 

Capture1.PNG

Thanks 🙂

1 ACCEPTED SOLUTION
karnold
Resolver I
Resolver I

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

karnold_0-1652225705260.png

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!

 

View solution in original post

6 REPLIES 6
karnold
Resolver I
Resolver I

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

karnold_0-1652225705260.png

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!

 

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 

Mohsin_Hassan_0-1654512748357.png

But it shows as 

Mohsin_Hassan_1-1654512843820.png

 

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?

 

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

This worked perfectly, thanks.

karnold
Resolver I
Resolver I

Hi

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

karnold_0-1652113021698.png

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:

karnold_1-1652113167784.png

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!

 

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?

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors