Thanks sweatpants. Very helpful. Just want to point out the "ending headcount" formula should be Ending Headcount = CALCULATE([All employees],FILTER('HR Data for Turnover','HR Data for Turnover'[Hire/Rehire Date]=LASTDATE('AE_RollingCalendar_Lookup'[Date]))))
I love how clearly you explained everything, I really appreciate this video! I'm new to BI, modeling, dax, you gave me a better understanding of some of the formula logic. However, for this task, i'm stuck. I've watched three tutorials today, all with varying methods for a monthly head count, but it still only returns a count of people hired in a given month - i'm not seeing the effect of active employees being rolled over month to month; there is no cumulation. it's the exact same outcome each method I try, so i think it has something to do with my data but I dont see how.
Thank for this great video ,please but could you give more details why you didn't join date table , because from thousands of videos and tutorial nobody ever use it like this .
My People Data fact table has multiple employee lifecycle events, and so the Headcount measure is not working In an ideal world, employees go through multiple lifecycle events such as Hire, Transfer, Promotion, Absence, Termination etc. and hence my table has multiple events for each employee and thus multiple records per employee Can you please suggest how your approach would work for a fact table which is actually a fact table given that it has multiple records? The People Fact table you used seems more like a Snapshot file wherein the current status of all employees has been stamped
There should be a start braces just after AND condition because either the relieving date will be after the LASTDATE or blank. Here is the correct DAX. Headcount = CALCULATE(COUNT(People_Fact[Employee Code]),FILTER(People_Fact,(People_Fact[Joining Date]LASTDATE(Date_Table[Date]) || People_Fact[Relieving Date]=Blank()))))
Thanks sweatpants. Very helpful. Just want to point out the "ending headcount" formula should be
Ending Headcount = CALCULATE([All employees],FILTER('HR Data for Turnover','HR Data for Turnover'[Hire/Rehire Date]=LASTDATE('AE_RollingCalendar_Lookup'[Date]))))
Thanks
This is absolutely bealtiful. thank you so much for sharing this with us.
I have been looking for this tutorial EVERYWHERE
This is nothing but awesome , I wish you will upload Pbx file as well
This tutorial is pure gold! Thanks a ton for putting it out there. You're absolutely legend.. subscribed ❤❤
I love how clearly you explained everything, I really appreciate this video! I'm new to BI, modeling, dax, you gave me a better understanding of some of the formula logic. However, for this task, i'm stuck. I've watched three tutorials today, all with varying methods for a monthly head count, but it still only returns a count of people hired in a given month - i'm not seeing the effect of active employees being rolled over month to month; there is no cumulation. it's the exact same outcome each method I try, so i think it has something to do with my data but I dont see how.
Great subject 👌. Thanks
Hi! Can we calculate the retention rate for other dimensions without bringing in the time period column?
Thank for this great video ,please but could you give more details why you didn't join date table , because from thousands of videos and tutorial nobody ever use it like this .
same question.
My People Data fact table has multiple employee lifecycle events, and so the Headcount measure is not working
In an ideal world, employees go through multiple lifecycle events such as Hire, Transfer, Promotion, Absence, Termination etc. and hence my table has multiple events for each employee and thus multiple records per employee
Can you please suggest how your approach would work for a fact table which is actually a fact table given that it has multiple records?
The People Fact table you used seems more like a Snapshot file wherein the current status of all employees has been stamped
I have the same problem! did you maybe manage to figure out how to do it?
There should be a start braces just after AND condition because either the relieving date will be after the LASTDATE or blank. Here is the correct DAX.
Headcount = CALCULATE(COUNT(People_Fact[Employee Code]),FILTER(People_Fact,(People_Fact[Joining Date]LASTDATE(Date_Table[Date]) || People_Fact[Relieving Date]=Blank()))))
Can you please help me with date file as I do not see it while downloading the data set file.
Where is the date table?
where can i find date file ???