Budget/Forecast/Actual Scenario Management

[Anonymous]

Hi all,

I need some advices on the best way to manage the following data modelling.

I have a fact table with actual sales figures coming from the ERP. I have then one or more spredsheets containing multip'le versions of budget and forecast. I have already managed the different file format and the different granularity issue.

What I need is the best way to manage the multiple BDG/FC versions (4 FC for each year and 1 or more BDG versions) and the actual. For example I vould like to compare monthly sales using past two actual years then budget and forecast for this year. See an example below for now based only on actual figures:

How can I do this? I can easily add a scenario field (A, B1, B2, B.., F1, F2, F3, F4) to each fact table but how can I play with visuals mixing some years from actuals and some form budget/forecast ?

Hope is clear enough... Many thanks in advance.

Regards,

C.

[v-cgao-msft]

Hi @Anonymous ,

Please try create a Calendar table and use the Calendar table to connect to the Fact tables. Then create measures based on different requirements, and then use those measures to create field parameters. Finally, create a Area Chart by using 'Calendar' [Month Number] as the x-axis and several field parameters as the y-axis.

Fields parameters in Power BI

Best Regards,
Gao
Community Support Team

If there is any 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 us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[polymathy2017]

Do you really think this very high-level answer is helpful for someone asking this question? I mean, if so, you can't be serious. 

[Anonymous]

Hi Gao, thanks for your reply.

I do already have a Date table connected to both fact tables (Actuals and BDG/FC). I would avoid to create measures for each scenario & FY combination as the model would have to be maintained at each new FY/scenario. This is the selection I would like the user to make:

At the moment the slicer above is fed by an independent/unconnected scenario table. The user can select multiple scenarios and the system should populate data from the correct fact table (Fact_Actuals for ACT and Fact_BDFC for Forecast and Budget scenarios.

Then the visual should be populated in the legend with the Scenario/FY in the X-axys with the period and in the Y-axys with the correct value coming from a calculated measure. What I miss is the code to calculate this measure.

Hope to have been clear enough and thanks for your support.

Regards,

C.