Us HR Data - Starting point tips

[JHJHJH1988]

Hello all

I'm fairly new to PowerBi and ive moved into a team that is looking to utilise its functionalities. We have a full fabric env and an engineering team that is helping our retail side of the business whilst i look into the HR side. 

I've been tasked to look at HR data from the US where i am reliant on a colleague sending me over monthly snapshots of headcount from may 22 and leaver data each month as i cannot gain access to their HRIS or payroll systems due to legal constraints.

I have attached a clean powerbi model that has the headcount and leaver data linked to my dimcalendar table which is linked to our dimcalendarslicer table.

I know i have not attempted to write any dax measures in this file as i am very unsure where to start. I'm after what i think are basic HR KPIS: 

1) count of headcount for every month since may 22

2) count of leavers for every month since may 22

3) rolling 12 month average of headcount from june 23 (as my headcount data starts form may 22, so that means the first average headcount would be jul 22 to june 23)

4) how to plot the above in a line graph where each month in the graph shows the rolling 12 average each month. Whenever i have attempted this with whatever measure i write it seems to populate the headcount for the month that forms part of the 12 month average and not the average over 12 months for that respective month!)

5) sum of voluntary leavers and involuntary leavers each month and rolling 12.

6) Comparitors to LY but i think i can solve this with Sameperiodlastyear functions.

We intend to utilise RLS so i dont think userelationships will work here.

I am really struggling with how to write up these basic measures and i am not sure how to even approach something like this. I have extesnive excel background where i have been able to dive in and learn but i cant seem to translate what i do in excel into pbi. I would really hope that someone could show me how these measures could be built and that i could study them to help me understand the way things can be done. 

I was unsure if i should post a sample file on the basis of even uploading a clean file onto google drives which our cyber team would not like therefore i've posted the tables below and a set of sample data where each row is like the sample

dimcalendar table is your usual table, where i have field Date:

headcount table:

Payroll Profile Code

Payroll Profile Desc

FasciaType

Clock Sequence #

Employee Name

Cost Center Desc

Cost Center Code

Hire Date

Position

DOL Status

Manager Level Code

Gender

EEO1 Ethnicity

Month

monthtest

0LR92

Brand 1

Fascia 1

19024

Employee 1

Store 1

4114

04/15/2002

Job title 1

Full Time

MAN

Female

Hispanic or Latino

01/03/2025

01/03/2025

leavers table

Payroll_Profile_Code

Payroll_Profile_Desc

FasciaType

ClockSeq_#

Employee_Name

Cost_Center_Desc

Cost_Center_Code

Hire_Date

Termination_Date

Termination_Type

TerminationReason

Length_of_Service_Since_Hire

Position

DOL_Status

Manager_Level

Gender

EEO1_Ethnicity

0LR93

Brand 7

fascia 5

18893

Employee 527

Store 21

4852

24/06/2024

31/12/2024

Voluntary

Resignation

0 Years 6 Months 7 days

Job title 80

Full-Time

MAN

Female

I do not wish to self-identify

[v-venuppu]

Hi @JHJHJH1988 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

Regards,

Rama U.

[Ashish_Mathur]

Hi,

It will help if you share a small summy dataset to work with and show the expected result.

[JHJHJH1988]

Hi Ashish

apologies for the delayed reply, please see link to a google drive doc that is an excel file of what i am trying to replicate in powerbi.

the data in the headcount and leaver tabs is how my HRIS extracts information. I then perform a group by funciton in powerquery to countrows by Cost Center Desc by each month. that gets me a headcount each month. From that i was intending on deriving a 12 average month each month. For example, the matest month headcount is 01/03/2025 - therefore i would add up each headcount month past 12 months by each Cost Center Desc and div by 12 (see columns D and E in the summary sheet). 

https://docs.google.com/spreadsheets/d/1jH4ju6eWs-8HXpflEntjvAfuOEXpg4qv/edit?usp=sharing&ouid=10148...

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[JHJHJH1988]

Hi Ashish

thank you for this file - it works fantastic. I've even managed to understand the measures a little bit and applied this to my real data to get the output needed. Its raised some eyebrows in terms of the data integrity that the US HR team uses (they have 1 cost centre assigned to multiple entitites which makes no sense!)

My stakeholders were wondering if its possible getting the same results using the date slicer. I've tried doing this but looking at the avg headcount rolling 12 month measure, it looks like if i selected dates 01/04/2024 - 01/05/2025 on the slicer, it takes the min date (so the april 24 date) and goes back 11 months. 

could this measure be modified such that it uses the date slicer instead?

avg headcount rolling 12 month = AVERAGEX(SUMMARIZE(CALCULATETABLE('Calendar',DATESBETWEEN('Calendar'[Date],EDATE(MIN('Calendar'[Date]),-11),MAX('Calendar'[Date]))),'Calendar'[FY],'Calendar'[Month name],"A",[Headcount Total]),[A])

edit

i think i've managed to do this by setting some VAR for start date, end date and then both of them as a Daterange:

avg headcount rolling 12 month dynamic =

VAR StartDate = MIN('Calendar'[Date])

VAR EndDate = Max('Calendar'[Date])

VAR DateRange = DATESBETWEEN('Calendar'[Date], StartDate, EndDate)

RETURN

AVERAGEX(

    SUMMARIZE(

        CALCULATETABLE('Calendar', DateRange),

        'Calendar'[FY],

        'Calendar'[Month name],

        "A", [Headcount Total]),[A])

[Ashish_Mathur]

You are welcome.  If my previous reply helped, please mark it as Answer.

[lbendlin]

You can follow this pattern New and returning customers – DAX Patterns