Calculate employee count in a given period

[eetigu]

Hi Guys,

I need assistance calculating the employee count in a given period of time. In my dataset,  I have the Personel Number, Contract Start Date, Contract End Date, and a termination reason. 

I would like to be able to represent this on a chart. 

Thanks

[eetigu]

Hi @Anonymous , Thanks for your response. I have tried the formula but it does not give the desired result as it is not cummulative. Below is an example table of the dataset. 

EmpID	StartDate	EndDate	Termination Code
51	Monday, February 3, 2020	Friday, February 3, 2023
54	Monday, June 10, 2019	Thursday, March 31, 2022	Termination
53	Monday, March 2, 2020	Thursday, March 2, 2023
55	Tuesday, October 1, 2019	Sunday, July 31, 2022	Termination
56	Monday, December 9, 2019	Sunday, October 31, 2021	Termination
57	Thursday, June 20, 2019	Wednesday, September 30, 2020	Termination

Thanks 

[Anonymous]

Hi,

Look at this link, I think that should help: https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=a7dd6505-...

[Anonymous]

Hi, I would probably need more info regarding table names and columns etc to fully fix this but I would follow this logic:

Count of Active Employee =
VAR currentDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            Table1,
            ( Table1[Start Date] <= currentDate
                && Table1[End Date] >= currentDate )
                && Table1[Active?] = 1
        )
    )

This is from another thread: https://community.powerbi.com/t5/Desktop/Calculating-a-monthly-employee-count-from-a-start-and-end-d...