Calculate Future Years values

[mdelalla]

Hi,
I'm very new to Power BI and I am trying to set up a table and a line chart to forecast headcount based on average number of new hires and average number of separations for the future 5 years (actuals are 2018-2022 and forecast data will be 2023-2027). The forecast for each year will be the actual headcount for previous year + avg new hires - avg separations. For example, headcount for 2022 is 100, avg new hires is 25 and avg separations is 20, then Headcount for 2023 will be 100+25-20 = 105, then headcount for 2024 will be 105+25-20, and so on.

Any help of how to create this would be really appreciated.

[mdelalla]

Hi Ashish,

The sum of Alpha is the headcount number for each year. Below is the link to the file, it is just a sample with little data on it.

https://app.powerbi.com/groups/me/reports/e08204fc-fcf4-4b2f-9979-b92ea0133e69/ReportSection?experie...

Thanks for your help

Regards

Marta

[Ashish_Mathur]

That takes me to a sign-in page.

[mdelalla]

Sorry about that, try this one.

https://1drv.ms/u/s!AoGWrXy4WRiC9y0M4taktbT3B91d?e=ScQk0O

Thanks!

Regards

Marta

[Ashish_Mathur · Accepted solution]

Hi,

Please find attached my solution file.

Hope this helps.

[mdelalla]

This is fantastic! it works perfectly, thanks so much!

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

Hi,

What is the Sum of Alpha column?  It sure does not look like the opening balance.  For 2023, what should the opening balance be and how have you calculated that?  Please clarify these doubts and share the download link of the PBI file.