calculate end head count by month with hire and rehire and previous termination

[ZURC_NAHOJ]

Hello all, 

I have a measure that works perfect to calculate my ending head counts for all employees except it doesn't recount an employee once they have been rehired.  in my image you see that i am looking to count them for every month they were here once they were terminated i stopped counting them and start counting them again based on their rehired date. i have one table with all this info.  

i have a total employees measure that counts each distinct empno. (for this case i filtered my data to one employee that has this scenario so i can figure it out. 

My ending head count measure is .

Ending Headcount = CALCULATE('_Measure'[Total Employees],

FILTER('V_EP_PR_EMP_ALL_FACT',V_EP_PR_EMP_ALL_FACT[DATE_HIRED]<FIRSTDATE('Date'[Date]) && V_EP_PR_EMP_ALL_FACT[TERMINATION_DATE]=BLANK()|| V_EP_PR_EMP_ALL_FACT[PREVIOUS_TERMINATION_DATE]>=LASTDATE('Date'[Date])))

i am lost as to how to enter the rehire variable in here to count and then recount them again. if you see my image i show the outcome i need. i also thought of creating a refence to my main table for just rehires but i am trying to stay on one table but will have to do what works. Thanks in advance for the help. 

[v-dineshya]

Hi @ZURC_NAHOJ ,

Thank you for reaching out to the Microsoft Community Forum.

Ending Headcount =
CALCULATE(
    DISTINCTCOUNT('V_EP_PR_EMP_ALL_FACT'[EMPNO]),
    FILTER(
        'V_EP_PR_EMP_ALL_FACT',
        'V_EP_PR_EMP_ALL_FACT'[DATE_HIRED] <= MAX('Date'[Date]) &&
        (
            ISBLANK('V_EP_PR_EMP_ALL_FACT'[TERMINATION_DATE]) ||
            'V_EP_PR_EMP_ALL_FACT'[TERMINATION_DATE] >= MIN('Date'[Date])
        )
    )
)

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[anmolmalviya05]

Hi @ZURC_NAHOJ, Please try the below measure:

Ending Headcount =
CALCULATE(
DISTINCTCOUNT(V_EP_PR_EMP_ALL_FACT[EMPNO]),
FILTER(
V_EP_PR_EMP_ALL_FACT,
V_EP_PR_EMP_ALL_FACT[DATE_HIRED] <= MAX('Date'[Date]) &&
(
ISBLANK(V_EP_PR_EMP_ALL_FACT[TERMINATION_DATE]) ||
V_EP_PR_EMP_ALL_FACT[TERMINATION_DATE] > MAX('Date'[Date])
)
)
)

If this post helps to answer your question, please consider accepting it as a solution so others can find it more quickly when they face a similar challenge.

Proud to be a Microsoft Fabric community super user

Let's Connect on LinkedIn

Subscribe to my YouTube channel for Microsoft Fabric and Power BI updates.

[ajaybabuinturi · Accepted solution]

Hi @ZURC_NAHOJ,

I am not sure how it will returns the data. However could ypu please try the below meassure to get distinct employee data. If possible could you please share the sample dataset so that I will get exact DAX.

Ending Headcount = 
CALCULATE (
    DISTINCTCOUNT ( 'V_EP_PR_EMP_ALL_FACT'[EMPNO] ),
    FILTER (
        'V_EP_PR_EMP_ALL_FACT',
        'V_EP_PR_EMP_ALL_FACT'[DATE_HIRED] <= MAX ( 'Date'[Date] )
            && ( 
                ISBLANK ( 'V_EP_PR_EMP_ALL_FACT'[TERMINATION_DATE] )
                || 'V_EP_PR_EMP_ALL_FACT'[TERMINATION_DATE] > MAX ( 'Date'[Date] )
                || 'V_EP_PR_EMP_ALL_FACT'[PREVIOUS_TERMINATION_DATE] > MAX ( 'Date'[Date] )
               )
            )
          )

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

[pankajnamekar25]

Hello @ZURC_NAHOJ 

try this measure

Ending Headcount =

CALCULATE(

    [_Measure'Total Employees],

    FILTER(

        'V_EP_PR_EMP_ALL_FACT',

        (

            'V_EP_PR_EMP_ALL_FACT'[DATE_HIRED] <= MAX('Date'[Date]) &&

            (

                ISBLANK('V_EP_PR_EMP_ALL_FACT'[TERMINATION_DATE]) ||

                'V_EP_PR_EMP_ALL_FACT'[TERMINATION_DATE] >= MIN('Date'[Date])

            )

        )

        ||

        (

            NOT(ISBLANK('V_EP_PR_EMP_ALL_FACT'[REHIRE_DATE])) &&

            'V_EP_PR_EMP_ALL_FACT'[REHIRE_DATE] <= MAX('Date'[Date])

        )

    )

)

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[ZURC_NAHOJ]

i still show that employee listed as part of the end head count in months where they are not here.