What if Parameter to forecast spend for the next five years

[raprabhakaran]

Hi All,

I am  trying to create a forecast measure that will calculate the spend over next five years. I have data until end of 2024. Below is the sample of my data:

Table Name: Master output

Posting Date	Cost Centre L1	Cost Centre L2	Cost Centre L3	Category	Supplier	Spend name	Value (€)
09/05/2023	Operations	Finance & Business	Procurement	Rent & Utilities	XYZ Wokplace and faciltiies	Workplace maintenance FY-23	€595,000
26/02/2024	Infrastructure	Major Programme	IT Change Management	IT	Snowflake	Data modernisation - Major Programme	€1,350,000
18/12/2024	CDO Office	Workplace Modernisation	Power Platform CoE	IT	Microsoft	Workplace optimisation - Vision 2030	€222,231

I have created a date table with the following formula: 

AutoC = CALENDAR(MINX('Master Output', 'Master Output'[Posting Date]), DATE(2030, 12, 31))

My challenge:

 I have to develop below visuals:

1. A parameter to modify the growth rate (I have learnt to create this using: WHAT IF PARAMETERS in Power BI I Forecasting Power BI Search Trends in 2021).
2. A parameter to control the #of years (if this cannot be automated as I am struggling to go beyond 2025 using the above video).
3. A line chart that breaks shows how different category will aggregate in spend:

   data on this can be found here:

   Year	Category	Value(Aggregate)	Forecast (aggregate)
   2023	Rent & Utilities	€1,000,000	€1,000,000
   2023	IT	€595,000	€595,000
   2024	Rent & Utilities	€1,250,000	€1,250,000
   2024	IT	€650,000	€650,000
   2025	Rent & Utilities		€1,290,000
   2025	IT		€700,000
   2026	Rent & Utilities		€1,450,900
   2026	IT		€750,000
   2027	Rent & Utilities		€1,350,000
   2027	IT		€780,000
   2028	Rent & Utilities		€1,540,000
   2028	IT		€795,000
   2029	Rent & Utilities		€1,650,000
   2029	IT		€890,000
   2030	Rent & Utilities		€1,690,000
   2030	IT		€950,000

4. A line chart that DOES NOT aggregate the forecast: 
5. 

Can someone please help me as I am trying to dynamically achieve this result but can't seem to move post 2025.

Thanks in advance.

[raprabhakaran · Accepted solution]

Hi Yilong - wish the solution was that straightfoward - but no - it requires using variables and what-if parameter. I was able to find the solution using chat-gpt. Thanks for trying though!

[Anonymous]

Hi @raprabhakaran ,

I create a table and a line chart as you mentioned.

So I think you can create a slicer and select what you want.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[raprabhakaran]

Hi @Anonymous - can you share the pbix on how did you do it? 

[Anonymous]

Hi @raprabhakaran ,

I think you can take a look about the .pbix file I upload.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.