Nice video but that is not how companies calculate turnover. Its total of leavers divided by average employees (for the period, be it month or year) x 100 to get the turnover percentage. How would you use dax to calculate that?
Wow, It's one of the best dashboard I have ever seen. In my dataset is one fact table with start date and end date of employees. Your solution about 2 fact tables connected by data calendar caused, that I don't have to use userrelationship functions in my measures. Thank you!
Hi Chandoo. This is a great video. Thank you. Regarding the Joiner and Leaver count. I have all my data in a single table with two columns (one for join date and one for termination date). Could you share information or link, etc on how to calcualte joiners and leavers if it is a single table?
If anyone face error at 13:29 "After removing duplicates > Change the type of date to Whole number > after that drill down > then type the formula = {#"Changed Type"{0}[Date of join] .. Number.From(Date.From(DateTime.LocalNow()))}, I hope this help!
Thanks for your wonderful work as always. I am facing one issue that my total staff to date is show data till 2021 year and the original data is till 2019. How the data is repeating automatically till 2021? Please reply sir.
Hi Chandoo, I have a similar data set and tried exactly the same process, however, my Leaver count and Joiner count is coming as a flat number. Need help please
Expression.Error: We cannot apply operator .. to types List and Number. Details: Operator=.. Left=[List] I'm getting this error while making the calender. What to do?
After removing duplicates > Change the type of date to Whole number > after that drill down > then type the above formula I mentioned, I hope this help!
Power BI automatically shows a line when you hover on points on line graphs. Is this what you mean? If not, can you point to the time in video so I can check.
Super, excellent, just one question how to find out active head count for any date. If we do filter then it will show filter active head count, so please help
@@chandoo_ Thank you so much sir, you are my life savior, you have no idea how much you have make my life easy. you're measures is correct only but first time I am facing some issues then second time I tried to copy same to same from yours then it's working fine. You are awesome 👍 keep it up
funny most of these presos have the same concept for dataset. Our datawarehouse gives out Active + Add + inactive (every month) so we need to figure how to advoid 80% from being double counted. :(
Hi Chandoo, could you please help me as I am trying to get percentage month wise for leaver, but outcome is coming in descending order that is incorrect. I have three tables First is a transaction table(Base Data), second is a fact table with starter, leaver date and status(ALL Employee), last one is a calendar table which is having just date, month, year etc(calendar date Table). Fact tables are having relationship with transaction table. The DAX, which I wrote is leaver count from fact table and divide by total number of employees. But the answer is wrong. (Leaver Count = CALCULATE(COUNT('ALL Employee'[Leaver Info]), FILTER('ALL Employee','ALL Employee'[Leaver Info]="Leaver"))) (HeadCount = DISTINCTCOUNT('Base data'[Employee Code]))
hmm.. I am using "turnover" and "attrition" synonymously. Not sure what is misleading here. Feel free to see the details on the sample page too - chandoo.org/wp/employee-turnover-dashboard-powerbi/
![How to build an Interactive HR Dashboard in Excel | HR Analytics in Excel [2024]](/img/1.gif)
Have seen so many videos on attrition but this is one of the best.. exactly what I needed.. Will try it out tomorrow. Thank You!
Sir, may god bless you, for you have saved my internship at Carrier Global Corporation 🙏🙏
It's my pleasure
Nice video but that is not how companies calculate turnover. Its total of leavers divided by average employees (for the period, be it month or year) x 100 to get the turnover percentage. How would you use dax to calculate that?
Wow, It's one of the best dashboard I have ever seen. In my dataset is one fact table with start date and end date of employees. Your solution about 2 fact tables connected by data calendar caused, that I don't have to use userrelationship functions in my measures. Thank you!
Thanks Michal...
Hi Chandoo. Thanks for sharing this nice work for us.
Great Teaching !!!
Hi Chandoo. This is a great video. Thank you. Regarding the Joiner and Leaver count. I have all my data in a single table with two columns (one for join date and one for termination date). Could you share information or link, etc on how to calcualte joiners and leavers if it is a single table?
If anyone face error at 13:29
"After removing duplicates > Change the type of date to Whole number > after that drill down > then type the formula = {#"Changed Type"{0}[Date of join] .. Number.From(Date.From(DateTime.LocalNow()))}, I hope this help!
Thanks for your wonderful work as always. I am facing one issue that my total staff to date is show data till 2021 year and the original data is till 2019. How the data is repeating automatically till 2021? Please reply sir.
Hi Chandoo, I followed all your steps but forecasting doesn't seems to be happening in last visual. It says data missing. How to troubleshoot this.
thank you so much sir....
What are the steps you did to setup relationships
Hi Chandoo, I have a similar data set and tried exactly the same process, however, my Leaver count and Joiner count is coming as a flat number. Need help please
Expression.Error: We cannot apply operator .. to types List and Number.
Details:
Operator=..
Left=[List]
I'm getting this error while making the calender. What to do?
= {#"Changed Type"{0}[Date of join] .. Number.From(Date.From(DateTime.LocalNow()))} use this It access first element of list {0}
After removing duplicates > Change the type of date to Whole number > after that drill down > then type the above formula I mentioned, I hope this help!
Hi Chandoo, how to get the letter Q suffixed in the quarter column?
My joinee count is coming same as Leavers count when I am sorting by Designation/Branch or any other field.
I am confused in this date part in power query 😢, can someone help me with the code
How do you show a vertical line in the graphs, as seen in the lesson?
Power BI automatically shows a line when you hover on points on line graphs. Is this what you mean? If not, can you point to the time in video so I can check.
@@chandoo_ Thank you very much!
How do we calculate Annualized Attrition Rate. In Excel we do Attrition Last 12 Month / Average (last 12 Months Headcount)?
Thank you, but I can't seem to get the green line on top the blue line on the line graph, after putting the date on the axis option
Where does it go?
@@chandoo_ it just gives a straight line
So on my dashboard, board both blue and green lines are straight parallel lines not curved like yours
@@tenolalpha5587 Check your Model > Manage Relationships > Calendar.
Super, excellent, just one question how to find out active head count for any date. If we do filter then it will show filter active head count, so please help
Hitesh... You can easily create such a measure as it is nothing but cumulative total of all hires to date minus all exits to date.
@@chandoo_ Thank you so much sir, you are my life savior, you have no idea how much you have make my life easy. you're measures is correct only but first time I am facing some issues then second time I tried to copy same to same from yours then it's working fine. You are awesome 👍 keep it up
Hi chandoo, please help to count average of active head count yearly, quarterly and monthly, please help 🙏
Dear sir, How to calculate moving average or running average of total current staff each month
You can use "Show value as" option on the chart measures to show running totals. Moving average is possible with a bit of DAX.
funny most of these presos have the same concept for dataset. Our datawarehouse gives out Active + Add + inactive (every month) so we need to figure how to advoid 80% from being double counted. :(
How did you also generate the forecast for the second line chart?
You can add forecast by selecting the line chart and using "Analytics" tab to apply forecast.
@@chandoo_ done but the line is not curved like the funnel shaped one on your second graph
Hi Chandoo, could you please help me as I am trying to get percentage month wise for leaver, but outcome is coming in descending order that is incorrect. I have three tables First is a transaction table(Base Data), second is a fact table with starter, leaver date and status(ALL Employee), last one is a calendar table which is having just date, month, year etc(calendar date Table). Fact tables are having relationship with transaction table. The DAX, which I wrote is leaver count from fact table and divide by total number of employees. But the answer is wrong.
(Leaver Count =
CALCULATE(COUNT('ALL Employee'[Leaver Info]),
FILTER('ALL Employee','ALL Employee'[Leaver Info]="Leaver")))
(HeadCount = DISTINCTCOUNT('Base data'[Employee Code]))
I am not sure if I got the model right. Can you email me the file? You can find the contact details in about section on YT or on my site.
How to get that formula editing after changing the type and drill down
Not sure I understand what you mean. If you want to edit a measure, simply select the measure in the field list and you can edit it from formula bar.
The video title says Attrition but after watching your video, I cannot see any DAX calculation about this? A bit misleading to be truthful
hmm.. I am using "turnover" and "attrition" synonymously. Not sure what is misleading here. Feel free to see the details on the sample page too - chandoo.org/wp/employee-turnover-dashboard-powerbi/
could you share the practice file.
Please visit this page - chandoo.org/wp/employee-turnover-dashboard-powerbi/ for the sample file.
Thank you sharing nice video but i was facing some challenges while creating calendar table. how can i solve this
Thank you. Can you tell me what problem you are facing?