Forum Discussion
DAX Formula for Beginning and Ending Headcount
Need help in my formula to return the Beginning and Ending Headcount
Beginning Headcount for December 2025 should be empty, while the Beginning Headount for
the succeeding month will be based from the Ending Headcount of the previous month. I ran different formula's but not getting anywhere.
I hard coded the the beginning headcount instead and this is how it should look like, however since it is hardcoded, it will not return the correct value when a filter selected.
| Beginning Head Count | Joiners Count | Ending Head Count | Average Head Count | |
| 2025 | 42 | 5977 | 2989 | |
| December | 42 | 5977 | 2989 | |
| 2026 | 5977 | 1233 | 5861 | 6012 |
| January | 5977 | 396 | 5947 | 5962 |
| February | 5947 | 467 | 6125 | 6036 |
| March | 6125 | 272 | 6055 | 6090 |
| April | 6055 | 98 | 5861 | 5958 |
This is the formula for the months with hard coded values:
Beginning Headcount:=iF(
HASONEVALUE('Data'[Month Name]),
Switch(
MAX(Data[Month Name]),
"January", 5977,
"February", 5947,
"March", 6125,
"April",6055,
BLANK()
),
CALCULATE(5977,Data[Month Name]="January")
)
while this is the initial formula I had however it does not return the beginning headcount correctly
Beginning Headcount:=[Ending Head Count]-[Joiners Count]+([Involuntary Count]+[Voluntary Count])
HI!
The key insight here is that Beginning Headcount = Ending Headcount of the previous month. So instead of hardcoding values, you can look up the previous month's ending headcount dynamically:
daxBeginning Headcount =
VAR CurrentMonth = MIN('Data'[Date])
VAR PrevMonthEnd =
CALCULATE(
[Ending Head Count],
DATESMTD(DATEADD('Date'[Date], -1, MONTH))
)
RETURN
IF(
HASONEVALUE('Data'[Month Name]),
PrevMonthEnd,
CALCULATE(
[Ending Head Count],
DATESMTD(DATEADD('Date'[Date], -1, MONTH)),
ALL('Data'[Month Name])
)
)
The logi: for any given month, go back one month and return the Ending Headcount from that period. December 2025 returns blank naturally because there's no November 2025 data. When filters are applied, DATEADD respects the filter context so it always looks at the correct previous month.
Make sure your Date table has a proper date column (not just a month name text column), otherwise DATEADD won't work correctly.
5 Replies
Hi ExodusFranz,
Thank you for reaching out to the Microsoft Fabric Community Forum, and thanks to the Ilgar_Zarbali and Juan-Power-bi for sharing helpful insights.
Just checking in, were you able to resolve the issue using the suggestions provided? If not, please feel free to share an update, and we’ll be happy to assist further.
Your feedback will also help others facing similar challenges.
Thank you!HI!
The key insight here is that Beginning Headcount = Ending Headcount of the previous month. So instead of hardcoding values, you can look up the previous month's ending headcount dynamically:
daxBeginning Headcount =
VAR CurrentMonth = MIN('Data'[Date])
VAR PrevMonthEnd =
CALCULATE(
[Ending Head Count],
DATESMTD(DATEADD('Date'[Date], -1, MONTH))
)
RETURN
IF(
HASONEVALUE('Data'[Month Name]),
PrevMonthEnd,
CALCULATE(
[Ending Head Count],
DATESMTD(DATEADD('Date'[Date], -1, MONTH)),
ALL('Data'[Month Name])
)
)
The logi: for any given month, go back one month and return the Ending Headcount from that period. December 2025 returns blank naturally because there's no November 2025 data. When filters are applied, DATEADD respects the filter context so it always looks at the correct previous month.
Make sure your Date table has a proper date column (not just a month name text column), otherwise DATEADD won't work correctly.I’m trying to understand the table. I think it might be something else.
I prepared a sample Excel source table and a Microsoft Power BI Desktop file. Please try them — I hope they will help.
If you consider this a solution, please mark it as the accepted solution. Thanks.
Hi ExodusFranz,
Just checking in to see if your issue has been resolved. Please let us know if you need any further assistance.
Thank you.
