Forum Discussion
Previous Month Calculation to Forecast
Hi all,
I have the below data:
| Month | Headcount | Hires | Other Increase | Other Decrease |
| February | 400 | 0 | 0 | |
| March | 20 | 10 | 20 | |
| April | 5 | 10 | 0 | |
| May | 10 | 0 | 0 |
Problem statement:
I need to create a calculation that sums up the headcount, hires, increases and decreases for future months.
I want March to take February's headcount and add March's respective Hires, Other Increases and minus Other Decreases.
For example March would be 400 + 20 +10 -20 = 410
April would then take March's value (410) and perform the same calculation: 410 +5 +10 - 0 = 425
May would then take Aprils number and so on...
I am having trouble trying to get each month to take the calculated vale of the month before. Please help and thank you in advance.
14 Replies
JamesFR06Resolver IV
Hi,
do you have a date table ?Hi JamesFR06 - yes i do have a date table. The date field in the date table has a relationship with the 'headcount' table via the headcounts 'month' field
The headcount table is a row per unique employee , per month, and my headcount calculation is a countrows(headcount table) and then aggregated monthly via the date table relationship.
The hires, other increases, and other decreases fields are from other tables and also aggregated to a monthly level via relationships with the date table with their tables respective date column.Let me know if that clarifies your question or if you need any further information.
Thanks
- JamesFR06
ib319961,
Thanks for the reply. And just to be sure you only have 1 year data in your model ?
JamesFR06 hi - i have data going to March 2025 in the date table so would like it to extend until the latest possible date in my date table, i'd only need to do the calculation for future months after my max month in my headcount table where i already have actuals. Thanks
- JamesFR06
The problem is if you link date and fact table by month it will not work. If you replace the month by a date it will work perfectly as longer as you want.
Can you replace the month by the date of this month with year of course ?
The below is what my data looks (same column order as table above) like with Month / Year coming from the date table.
I want March to pull February 2024 headcount (row above which i've cropped out in the screenshot due to data scrubbing) and add the three columns to the right of it. Eg if February 2024 was 400, it would be 400 +4 = 404. I'd then want April to use 404 as the base number and performn the calculation again, eg. 404 + 1 = 405.
Previous month calculations would mean that April would take the headcount for march which is currently 0 in my data, but i'd want it to take the calculated 404 from March looking up february. Let me know if that makes sense?