Forum Discussion
Forecasting with DAX
| Headcount | Hired | Leaving | Forecast | |
| May | 8 | 0 | 0 | |
| Jun | 8 | 1 | 1 | |
| Jul | 3 | 0 | 11 | |
| Aug | 1 | 2 | 10 |
- Each column is from a different source (connected through a date table)
Hello! I'm doing a headcount report, but am unfortunately quite new to powerbi.
the total headcount numbers come from an end-of-month sheet, so obviously July and August aren't available yet!
I'd like to forecast numbers (PREVIOUS month's headcount + hired - leavers) with a measure and not a visual calculation.
How do I connect headcount & forecast numbers in a single measure with DAX?
I was thinking of things like
Headcount (actual & forecasted):
If month< today, show headcount as per usual
If month > today, do the calculation of previous month + this month's hires - leavers
But I can't exactly figure out how 😢 thanks for any help
Hi kowas2
Thank you for providing the information.
To calculate the headcount, start with the last month that has an actual headcount and add the cumulative net movement (Hired minus Leaving) up to the month you want to review.
For instance, if June is the last actual month with a headcount of 8:
July = 8 + (Jul Hired - Jul Leaving)
August = 8 + (Jul Hired - Jul Leaving) + (Aug Hired - Aug Leaving)
This approach keeps the calculation tied to the last actual headcount and tracks all subsequent changes month by month.
In DAX, you would store the last actual headcount in a variable, calculate the cumulative total of [Hired] minus [Leaving] from the month after the last actual month through the current month, and return:
LastActualHeadcount + CumulativeNetMovement
This method should give you the expected values for July and August.
I hope this helps clarify things. If there’s anything I may have misunderstood any part of your query, please let us know.
Regards,
Microsoft Fabric Community Support Team.
9 Replies
Hi .
Step 1 base measuresHeadcount Actual :=SUM('HeadcountTable'[Headcount])Hired :=SUM('HiredTable'[Hired])Leavers :=SUM('LeaversTable'[Leaving])Step 2 find the last month with real dataLast Actual Month :=CALCULATE(MAX('Date'[Date]),FILTER(ALL('Date'),NOT ISBLANK([Headcount Actual])))Step 3 final measure (Actual + Forecast)Headcount Forecast :=VAR CurrentMonth = MAX('Date'[Date])VAR LastActual = [Last Actual Month]RETURNIF(CurrentMonth <= LastActual,[Headcount Actual],CALCULATE([Headcount Actual],FILTER(ALL('Date'), 'Date'[Date] = LastActual))+CALCULATE(SUMX(FILTER(ALL('Date'),'Date'[Date] > LastActual &&'Date'[Date] <= CurrentMonth),[Hired] - [Leavers])))How it works: If the month has real data then return actual headcount. If the month is in the future: start from the last real value add all (Hired - Leavers) until the current month
Best practices:
-
Make sure your date table is correct and marked as a Date table
-
Relationships between tables must be active
-
Using measures (not calculated columns) is a good choice 👍
I'm thinking of using a cumulative total hires & leavers?
like
july forecast: 8+1-2 = 7
aug: 8+1-2+5-1= 11
hello, thanks so much for your reply!!
it works up to here:
RETURN
IF(
CurrentMonth <= LastActual,
[Headcount Actual],
however, it cant seem to add the previous forecast to the following month
(e.g.
june: 8
july forecast: 8 + 1 - 2 = 7
august forecast: uses 8 (from June) and doesn't add cumulative hires & leavers
hence, it forecasts correctly for july. however, from august onwards, it returns negative numbers
-
Hi,
You can achieve this with a measure that uses the actual headcount for past/current months and then forecasts future months recursively as:
Previous Month Headcount + Hired - Leaving
Since your tables are connected through a Date table, try something along these lines:
Headcount (Actual + Forecast) =
VAR CurrentMonth =
MAX ( 'Date'[Date] )
VAR LastActualMonth =
CALCULATE (
MAX ( 'Date'[Date] ),
FILTER (
ALL ( 'Date' ),
NOT ISBLANK ( [Headcount] )
)
)
RETURN
IF (
CurrentMonth <= LastActualMonth,
[Headcount], -- show actuals
VAR PreviousMonthValue =
CALCULATE (
[Headcount (Actual + Forecast)],
DATEADD ( 'Date'[Date], -1, MONTH )
)
RETURN
PreviousMonthValue + [Hired] - [Leaving]
)
How this works
- Past / actual months → shows your existing [Headcount] measure.
- Future months (Jul, Aug, etc.) → calculates:
Previous Month Headcount + Hired - Leaving
Using your example:
Month
Headcount
Hired
Leaving
Forecast
May
8
1
1
8
Jun
8
2
1
8
Jul
—
3
0
9
Aug
—
0
0
9
A couple of things to check:
- Your Date table should have a proper continuous month relationship.
- [Headcount], [Hired], and [Leaving] should already be measures (not raw columns).
- If your "actual" month is only loaded up to June, the measure will automatically start forecasting from July onward.
Hope this helps
Thanks!
thank you so much for your reply! this kind of worked, but i have trouble with this part:
VAR PreviousMonthValue =
CALCULATE (
[Headcount (Actual + Forecast)],
DATEADD ( 'Date'[Date], -1, MONTH
I wasn't able to make the measure self reference itself
Hi kowas2
Thank you for providing the information.
To calculate the headcount, start with the last month that has an actual headcount and add the cumulative net movement (Hired minus Leaving) up to the month you want to review.
For instance, if June is the last actual month with a headcount of 8:
July = 8 + (Jul Hired - Jul Leaving)
August = 8 + (Jul Hired - Jul Leaving) + (Aug Hired - Aug Leaving)
This approach keeps the calculation tied to the last actual headcount and tracks all subsequent changes month by month.
In DAX, you would store the last actual headcount in a variable, calculate the cumulative total of [Hired] minus [Leaving] from the month after the last actual month through the current month, and return:
LastActualHeadcount + CumulativeNetMovement
This method should give you the expected values for July and August.
I hope this helps clarify things. If there’s anything I may have misunderstood any part of your query, please let us know.
Regards,
Microsoft Fabric Community Support Team.
