Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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:
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 |
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.
Solved! Go to 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!
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!
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.
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
120 | |
74 | |
72 | |
58 | |
50 |
User | Count |
---|---|
167 | |
83 | |
68 | |
66 | |
55 |