Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Generally, in attrition analysis - an important leg is "Tenure wise attrition" to analyse which tenure people are moving out at a more / less rate.
The data that i have is as below:
List of all employees that have joined historically (indicated in data as joining date) and left (indicated as leaving date) along with other characterstics (e.g. Gender, Department etc)
To calculate attrition,
- I need to have Head Count (HC) : i can calculate that by knowing cummulative joinees till that month minus cummulative leavers.
- I need to have Leavers for that particular month - which is simple.
- I can calculate these values for various months.
But now if I have to calculate "Tenure wise attrition", I need to know the HC for that particular Tenure Bucket for e.g. 6 to 12 months. This could be calculated simply if i was calculating tenure as of now (fixed date) as now() - (date of joining or date of leaving)
But here i would like to calculate tenure on ANY selected month. How do i do it ?
Sample Data and Calculation is here > Click Here
Request to help / clarify if this is possible
PS: I had asked for clarification in another thread. Posting it as a separate question to seek answer as the situation remains un-resolved.
Solved! Go to Solution.
hi, @karlosdsouza
First, you should know that calculated column and calculate table can't be affected by any slicer. you could create a measure instead of column.
Notice:
1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.
here is reference:
https://community.powerbi.com/t5/Desktop/Different-between-calculated-column-and-measure-Using-SUM/t...
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/
Second, try this way as below:
Step1:
Add a date table as a slicer
Step2:
Use this formula to create a
Measure = VAR _tenure = IF ( SELECTEDVALUE ( Table1[Status] ) = "Active", CALCULATE ( SUMX ( Table1, CALCULATE ( ( MAXX('Date',SELECTEDVALUE('Date'[Date],NOW())) - SUM ( Table1[joining date] ) ) / 30 ) ) ), CALCULATE ( SUMX ( Table1, CALCULATE ( ( SUM ( Table1[leaving date] ) - SUM ( Table1[joining date] ) ) / 30 ) ) ) ) RETURN _tenure
Step3:
Create a group table
Step4:
Use these formulae to create the result measure
HC = var _table=FILTER(GENERATE(table1,table2),[Measure]>Table2[Start]&&[Measure]<=Table2[End]) return COUNTAX(_table,[Kind])+0
Attrition = var _table=FILTER(GENERATE(table1,table2),[Measure]>Table2[Start]&&[Measure]<=Table2[End]) return COUNTAX(FILTER(_table,[Status]="Left"),Table2[Kind])+0
Result:
Note: you could use date field as a slicer or create other fields eg. year-month in date table then use it as a slicer.
And here is pbix file, please try it.
Best Regards,
Lin
Hello, it is very nice dashboard sample. could u explain what's the tenura( bins )means?
hi, @karlosdsouza
First, you should know that calculated column and calculate table can't be affected by any slicer. you could create a measure instead of column.
Notice:
1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.
here is reference:
https://community.powerbi.com/t5/Desktop/Different-between-calculated-column-and-measure-Using-SUM/t...
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/
Second, try this way as below:
Step1:
Add a date table as a slicer
Step2:
Use this formula to create a
Measure = VAR _tenure = IF ( SELECTEDVALUE ( Table1[Status] ) = "Active", CALCULATE ( SUMX ( Table1, CALCULATE ( ( MAXX('Date',SELECTEDVALUE('Date'[Date],NOW())) - SUM ( Table1[joining date] ) ) / 30 ) ) ), CALCULATE ( SUMX ( Table1, CALCULATE ( ( SUM ( Table1[leaving date] ) - SUM ( Table1[joining date] ) ) / 30 ) ) ) ) RETURN _tenure
Step3:
Create a group table
Step4:
Use these formulae to create the result measure
HC = var _table=FILTER(GENERATE(table1,table2),[Measure]>Table2[Start]&&[Measure]<=Table2[End]) return COUNTAX(_table,[Kind])+0
Attrition = var _table=FILTER(GENERATE(table1,table2),[Measure]>Table2[Start]&&[Measure]<=Table2[End]) return COUNTAX(FILTER(_table,[Status]="Left"),Table2[Kind])+0
Result:
Note: you could use date field as a slicer or create other fields eg. year-month in date table then use it as a slicer.
And here is pbix file, please try it.
Best Regards,
Lin
How to show "Active" and "Leave"? for example people that are on leave but are still Active, any suggestions? 🙂
Measure = VAR _tenure = IF ( SELECTEDVALUE ( Table1[Status] ) = "Active", CALCULATE ( SUMX ( Table1, CALCULATE ( ( MAXX('Date',SELECTEDVALUE('Date'[Date],NOW())) - SUM ( Table1[joining date] ) ) / 30 ) ) ), CALCULATE ( SUMX ( Table1, CALCULATE ( ( SUM ( Table1[leaving date] ) - SUM ( Table1[joining date] ) ) / 30 ) ) ) ) RETURN _tenure
@v-lili6-msft : In the proposed solution, the "tenure" and tenure bucket doesnt change
Can you please explain how does it work ?
hi, @karlosdsouza
the "tenure" and "tenure bucket" are columns not measure and Date slicer has no relationship with "table1",
so they don't change.
Best Regards,
Lin
Hi @v-lili6-msft - So are you saying that the problem is not yet solved ?
I am little new to power BI. Request to please help - How do we get tenure wise attrition %
hi, @karlosdsouza
tenure wise attrition % is based on another table visual and measure is "tenure"
By the way: there is a little bit of modification in my pbix file, for date table it should be a full date. Use this formula to create a date table
Date = CALENDAR(DATE(YEAR(MIN(Table1[joining date])),1,1),DATE(YEAR(TODAY()),12,31))
and for the slicer, you should filter one day as a filter.
Otherwise, if you select a period of time, how to calculate the measure.
Best Regards,
Lin
Thanks ! Understood
If i make a new real table instead of virtual table. Will it make a difference @v-lili6-msft ?
Also if the selected value is less than Date of Joining, the tenure will come to be negative. How do we treat that ?
Thyanks so much v-lili6-msft !
Will get back after implementing the given solution
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |