cancel
Showing results for
Did you mean:
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 ?

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
Highlighted
Community Support Team

## Re: Attrition Dashboard - Tenure wise

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

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
Highlighted
Community Support Team

## Re: Attrition Dashboard - Tenure wise

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

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.
Regular Visitor

## Re: Attrition Dashboard - Tenure wise

Will get back after implementing the given solution

Regular Visitor

## Re: Attrition Dashboard - Tenure wise

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

Can you please explain how does it work ?

Community Support Team

## Re: Attrition Dashboard - Tenure wise

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.
Regular Visitor

## Re: Attrition Dashboard - Tenure wise

Community Support Team

## Re: Attrition Dashboard - Tenure wise

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

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.
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 ?