Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
raprabhakaran
Frequent Visitor

What if Parameter to forecast spend for the next five years

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 DateCost Centre L1Cost Centre L2Cost Centre L3CategorySupplierSpend nameValue (€)
09/05/2023OperationsFinance & BusinessProcurementRent & UtilitiesXYZ Wokplace and faciltiiesWorkplace maintenance FY-23€595,000
26/02/2024InfrastructureMajor ProgrammeIT Change ManagementITSnowflakeData modernisation - Major Programme€1,350,000
18/12/2024CDO OfficeWorkplace ModernisationPower Platform CoEITMicrosoftWorkplace 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:raprabhakaran_0-1736344952269.png

    data on this can be found here:

    YearCategoryValue(Aggregate)Forecast (aggregate)
    2023Rent & Utilities€1,000,000€1,000,000
    2023IT€595,000€595,000
    2024Rent & Utilities€1,250,000€1,250,000
    2024IT€650,000€650,000
    2025Rent & Utilities €1,290,000
    2025IT €700,000
    2026Rent & Utilities €1,450,900
    2026IT €750,000
    2027Rent & Utilities €1,350,000
    2027IT €780,000
    2028Rent & Utilities €1,540,000
    2028IT €795,000
    2029Rent & Utilities €1,650,000
    2029IT €890,000
    2030Rent & Utilities €1,690,000
    2030IT €950,000
  4. A line chart that DOES NOT aggregate the forecast: 
  5. raprabhakaran_1-1736345324838.png

 

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.

 
1 ACCEPTED SOLUTION
raprabhakaran
Frequent Visitor

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!

View solution in original post

4 REPLIES 4
raprabhakaran
Frequent Visitor

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!

v-yilong-msft
Community Support
Community Support

Hi @raprabhakaran ,

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

vyilongmsft_0-1736389649764.png

vyilongmsft_1-1736389744090.png

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

vyilongmsft_2-1736389954887.png

 

 

 

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.

Hi @v-yilong-msft - can you share the pbix on how did you do it? 

Hi @raprabhakaran ,

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

vyilongmsft_0-1736494520359.png

 

 

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.