Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
karlosdsouza
Helper II
Helper II

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
v-lili6-msft
Community Support
Community Support

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.

View solution in original post

9 REPLIES 9
Clarafang
Frequent Visitor

Hello, it is very nice dashboard sample. could u explain what's the tenura( bins )means? 

v-lili6-msft
Community Support
Community Support

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.

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 Screenshot_1.png

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

 

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.

 

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.

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 ?

Will get back after implementing the given solution

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.