Total Employee Count by Month

[Anonymous]

Hello. 

I am trying to get a total employee count by month. So the idea is that I would have:

(New Hires + Existing Employees) - Terminations = Total Employees per month

I have a data table with each employees hire and term dates, but I can't seem to get Power BI to include an employee beyond their hire month. 

TIA!

[Jihwan_Kim · Accepted solution]

Hi,

I tried to create a sample pbix file like below.

I suggest having a Dim Date table, and the data model like below.

Please check the below picture and the attached pbix file.

Employee count: =
COUNTROWS (
    FILTER (
        Employee,
        Employee[Hire Date] <= MAX ( 'Date'[Date] )
            && OR (
                Employee[Terminatino Date] >= MIN ( 'Date'[Date] ),
                Employee[Terminatino Date] = BLANK ()
            )
    )
)