Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
eetigu
New Member

Calculate employee count in a given period

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

 

 

3 REPLIES 3
eetigu
New Member

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. 

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

 

Thanks 

Anonymous
Not applicable

Anonymous
Not applicable

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...

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.