Forum Discussion

nareshbs18's avatar
nareshbs18
New Member
4 months ago
Solved

Dynamic Headcount Calculation Ignoring Date Context but Respecting Slicers in Power BI

I am trying to calculate starting and ending headcount per month using a Date table.
My HC table contains employee joining dates (and optionally leaving dates). The challenge is to calculate cumulative headcount while ensuring slicers (like Department, Location) still work.
Currently, using ALL() breaks slicers.

HC Table

EmployeeID

DOJ (Date of Joining)

LWD (optional)

Date Table

Date

Month

Year

Relationship

DateTable[Date] → HC[DOJ] (Active)

What I Want / Expected Result - 

For each month:

Starting HC = Employees active at beginning of month

Ending HC = Employees active at end of month

Should respond to slicers like Department, Location

 

Need Help
  • cengizhanarslan's avatar
    cengizhanarslan
    4 months ago

    Please try the measures below:

    Ending HC =
VAR _EndOfMonth =
    EOMONTH ( MAX ( 'Date'[Date] ), 0 )
RETURN
    CALCULATE (
        COUNTROWS ( HC ),
        REMOVEFILTERS ( 'Date' ),
        HC[DOJ] <= _EndOfMonth,
        OR (
            ISBLANK ( HC[LWD] ),
            HC[LWD] > _EndOfMonth
        )
    )
    Starting HC =
VAR _StartOfMonth =
    DATE ( YEAR ( MIN ( 'Date'[Date] ) ), MONTH ( MIN ( 'Date'[Date] ) ), 1 )
RETURN
    CALCULATE (
        COUNTROWS ( HC ),
        REMOVEFILTERS ( 'Date' ),
        HC[DOJ] < _StartOfMonth,
        OR (
            ISBLANK ( HC[LWD] ),
            HC[LWD] >= _StartOfMonth
        )
    )

5 Replies

  • cengizhanarslan's avatar
    cengizhanarslan
    Icon for Super User rankSuper User
    4 months ago

    Please try the measures below:

    Ending HC =
VAR _EndOfMonth =
    EOMONTH ( MAX ( 'Date'[Date] ), 0 )
RETURN
    CALCULATE (
        COUNTROWS ( HC ),
        REMOVEFILTERS ( 'Date' ),
        HC[DOJ] <= _EndOfMonth,
        OR (
            ISBLANK ( HC[LWD] ),
            HC[LWD] > _EndOfMonth
        )
    )
    Starting HC =
VAR _StartOfMonth =
    DATE ( YEAR ( MIN ( 'Date'[Date] ) ), MONTH ( MIN ( 'Date'[Date] ) ), 1 )
RETURN
    CALCULATE (
        COUNTROWS ( HC ),
        REMOVEFILTERS ( 'Date' ),
        HC[DOJ] < _StartOfMonth,
        OR (
            ISBLANK ( HC[LWD] ),
            HC[LWD] >= _StartOfMonth
        )
    )
  • v-moharafi-msft's avatar
    v-moharafi-msft
    Icon for Community Support rankCommunity Support
    4 months ago

    Hi nareshbs18  ,


    Thank you for reaching out to Microsoft Fabric Community and Thanks to cengizhanarslan   for Sharing valuable insights.

     

    Just wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. 


    Best Regards,

    Abdul Rafi.

  • v-moharafi-msft's avatar
    v-moharafi-msft
    Icon for Community Support rankCommunity Support
    4 months ago

    Hi nareshbs18 ,

     

    Could you please confirm if the issue has been resolved? If not, feel free to reach out if you have any further questions.

    Your update would be helpful for other members who may face a similar issue.

     

    Best Regards,

    Abdul Rafi