Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am a new user of PowerBI but other threads do not seem to assist with my exact searches.
I have an overall employee data set that splits our organisation by Org, Department, Teams, Job Level etc - I then have start dates and end dates for all employees. Similar to below:
Employee Name | Organisation | Department | Team | Job Level | Job Title | Start Date | End Date |
Bob Smith | Doodles | Marketing | Social Media | 4 | Lead Social Media Organiser | 08/02/2022 | |
Clare Smith | Doodles | Marketing | Social Media | 5 | Assistant Social Media Organiser | 01/03/2022 | 03/02/2022 |
I am trying to run a power query from our HR system so that each month I can report on our current headcount. All posts that I have come across so far show only reports which already have a headcount report that is being generated - would anyone be able to assist?
I've tried a few calculations from other posts which do not seem to return anything when I start putting the measure into visuals - I have a full date table in my PowerBI which I have tried methods of both linked and unlinked to the data set and it still returns blank. Any assistance would be great.
Thanks
Hi @HRAnalyst ,
You can use this measure:
Measure =
CALCULATE (
COUNTROWS ( 'Table' ),
'Table'[Start Date] <= MAX ( 'Date'[Date] ),
'Table'[End Date] >= MIN ( 'Date'[Date] )
|| ISBLANK ( 'Table'[End Date] )
)
I assume you have a Date table not connected to the Data table.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
hello @ERD its a smaller measure but it doesnt count it correctly on the month they left , so if you select march 2022 you will have 2 headcount instead of one
So you don't want to count the month they left? Because from your first screenshot I see 3 people, as one of them was working 2 days in March.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
im not sure but lets say you have a calendar table and just start date and end date
create the emasure
headcount =
VAR _r =
SUMMARIZE(
'Table',
'Table'[Employee Name],
"left?",
IF(
MAX('Table'[End Date]) <> BLANK() &&
MONTH(MAX('CALENDAR'[Date])) >= MONTH(MAX('Table'[End Date]))
, "Yes" , "No"),
"started?",
IF(
MONTH(MAX('Table'[Start Date])) <= MONTH(MAX('CALENDAR'[Date]))
, "Yes" , "No")
)
VAR _t = ADDCOLUMNS(_r,"Cond" , IF( [left?] = "No" && [started?] = "Yes" , 1,0))
return
COUNTROWS(FILTER(_t, [Cond] = 1))
end result
What does your table diagram look like? It all depends on your data structure.
User | Count |
---|---|
53 | |
35 | |
20 | |
15 | |
14 |
User | Count |
---|---|
94 | |
76 | |
36 | |
22 | |
18 |