Forum Discussion

ExodusFranz's avatar
ExodusFranz
New Member
3 months ago
Solved

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 f...
Need Help
  • Juan-Power-bi's avatar
    Juan-Power-bi
    3 months ago

    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.