DAX Formula for Beginning and Ending Headcount

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.