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