HR dashboard

[maryam_arif28]

Dear Expert,

I am making  dashboard, where i have to 4 tables employee master, employee card, department and salary register. i have created a date table, 

now i have to calcualte 

1. Headcount

2. Male employee

3. female employee

this should be working with Date,Year and month filter.

I have two dates in my employee master one is[ employment date] and another is termination date

Employee ID

First Name

Middle Name

Last Name

addressLine1

addressLine2

State

Country

Postal Code

mobilePhone

Employment Date

Termination Date

status

E04

Sanskrati

Sharma

DSO

us

1/1/2019

3/12/2021

Active

E-0012

SARA

Thomas

us

1/1/0001

1/1/0001

Active

E-003

Alex

John

us

1/1/2019

1/1/0001

Active

then i have department table, i want to caclulate headcount, male and female employee when i click on year or month or quater then they should be changing. I do not want to make any connection because if i have to make then i have tow inactive connection kindlly help, I am on my deadline

Regards,

Arif

[Anonymous · Accepted solution]

Hi @maryam_arif28 ，

First, could you please provide the logic for calculating head count, female employee and Male employee? Which table does the gender information of the employee exist in? If there are no other special filter conditions, and assuming the employee gender information is in the employee card table, you can create the following three measures to achieve this. Please find the details in the attachment.

HR Dashboard Help - HC numbers

Headcount = 
VAR _seldate =
    SELECTEDVALUE ( 'Date'[Date] )
RETURN 
    CALCULATE (
        DISTINCTCOUNT ( 'employee master'[Employee ID] ),
        FILTER (
            'employee master',
            'employee master'[Employment Date] <= _seldate
                && (ISBLANK('employee master'[Termination Date])||('employee master'[Termination Date] > _seldate))
        )
    )

female employee = CALCULATE([Headcount],FILTER('employee card','employee card'[Gender]="Female"))

Male employee = CALCULATE([Headcount],FILTER('employee card','employee card'[Gender]="Male"))

If the measures given above do not meet your needs, please provide sample data from the four tables, the calculation logic and the final result you want to obtain. Thank you.

Best Regards

[amitchandak]

@maryam_arif28 , Please see the blog and attached file can help

You can add filter for gender to get male and female employee

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...