[Pearl Patodia]

Hi All,

I am new to Power BI. I am working on HR report where it is require to find employee head count by month. I have only one table where I have ‘Hire Date’ and ‘Terminated Date’. Can you please help me calculating head count of emp by month? Also I need to find How many projects are active.
I dont have sample data.

Thanks in advance!
Pearl

——————————
Pearl Patodia
——————————

[Sergio Murru]

Hi Pearl,

Here is a simple sample model with a Date table and a table with the two dates for hire and termination.

The following DAX code is a straightforward implementation for the head count in the selected months interval. Selecting a matrix visual with “year month” per rows you can get the head count per month. Please, note that I chose to include partial months, so I had to calculate the first day of the month and the last one.

Head Count = 
COUNTROWS (
    FILTER (
        'HR Table',
        DATE ( YEAR ( 'HR Table'[Hire date] ), MONTH ( 'HR Table'[Hire date] ), 1 )
            <= FIRSTDATE ( Dates[Date] )
            && (
                ISBLANK ( 'HR Table'[Terminated date] )
                    || EOMONTH ( 'HR Table'[Terminated date], 0 ) >= LASTDATE ( Dates[Date] )
            )
    )
)?

I hope this helps 🙂

——————————
Sergio Murru
TORINO
——————————
——————————————-

[Sergio Murru]

Hi Pearl,

to have the monthly count of hires and terminations you can create 2 disabled relationship in the model, between the date table and the dates in you HR table.
Then you can create two measures to count the hires and the terminations in the selected period (the month, in our matrix) using the corresponding relationships

this is the model

and the following are the two measures written in DAX

# Hired = 
CALCULATE (
    COUNTROWS ( 'HR Table' ),
    USERELATIONSHIP ( Dates[Date], 'HR Table'[Hire date] )
)

# Terminated = 
CALCULATE (
    COUNTROWS ( 'HR Table' ),
    USERELATIONSHIP ( Dates[Date], 'HR Table'[Terminated date] ),
    NOT ISBLANK ( 'HR Table'[Terminated date] )
)

and this is the updated matrix

I’ve attached the updated pbix file.

Please, let me know if this solutions suites your needs.

——————————
Sergio Murru
TORINO
——————————
——————————————-