cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
karlosdsouza Regular Visitor
Regular Visitor

Attrition Dashboard - Tenure wise

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Attrition Dashboard - Tenure wise

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

 

4.JPG

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:

5.JPG

 

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 REPLIES 7
Community Support Team
Community Support Team

Re: Attrition Dashboard - Tenure wise

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

 

4.JPG

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:

5.JPG

 

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
karlosdsouza Regular Visitor
Regular Visitor

Re: Attrition Dashboard - Tenure wise

Will get back after implementing the given solution

karlosdsouza Regular Visitor
Regular Visitor

Re: Attrition Dashboard - Tenure wise

@v-lili6-msft : In the proposed solution, the "tenure" and tenure bucket doesnt change Screenshot_1.png

Can you please explain how does it work ?

Community Support Team
Community Support Team

Re: Attrition Dashboard - Tenure wise

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

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
karlosdsouza Regular Visitor
Regular Visitor

Re: Attrition Dashboard - Tenure wise

 

Community Support Team
Community Support Team

Re: Attrition Dashboard - Tenure wise

hi, @karlosdsouza 

tenure wise attrition % is based on another table visual and measure is "tenure"

11.JPG

8.JPG

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.

12.JPG

Otherwise, if you select a period of time, how to calculate the measure.

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
karlosdsouza Regular Visitor
Regular Visitor

Re: Attrition Dashboard - Tenure wise

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 ?