Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Total Employee Count by Month

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!

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    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 ()
            )
    )
)

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    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 ()
            )
    )
)