Forum Discussion
Us HR Data - Starting point tips
Hi,
PBI file attached.
Hope this helps.
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).
Hi,
PBI file attached.
Hope this helps.
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)RETURNAVERAGEX(SUMMARIZE(CALCULATETABLE('Calendar', DateRange),'Calendar'[FY],'Calendar'[Month name],"A", [Headcount Total]),[A])You are welcome. If my previous reply helped, please mark it as Answer.