Forum Discussion
Calculate Employees Turnover
Thank you Eric. The inline hierarchy labels are very useful. However, I still cannot figure out how to use them to calculate and visulize the turnover through the five levels of hierarchy. :catmad:
In other words, how should I build my DAX function to make it work?
Thank you
Hello.
In my dataset i use this base mesures:
1) To count headcount per day and then average from it. For one day the result will be the sum, but for any wider segments it will be average.
CALCULATE (
AVERAGEX (
ADDCOLUMNS (
VALUES ( 'Calendar'[Date] );
"HC"; SUMX (
FILTER (
'Base';
'Base'[Start date of the period] <= 'Calendar'[Date]
&& 'Base'[End date of the period] >= 'Calendar'[Date]
);
'Base'[Headcount]
)
);
[HC]
);
ALL ( 'Base'[Company attitude to dismissal]; 'Base'[Reason for dismissal] )
)
I use ALL ( 'Base'[Company attitude to dismissal]; 'Base'[Reason for dismissal] )
to calculate the average number in any sections, but without taking into account the details on the fields 'Base'[Company attitude to dismissal] (regret, non regret) and 'Base'[Reason for dismissal].
2) To count the average for the rolling year
a) in Calendar table add column to count day number from the calendar start date:
SequentialDayNumber
COUNTROWS (
FILTER (
ALL ( 'Calendar' );
'Calendar'[Date] <= EARLIER ( 'Calendar'[Date] )
&& NOT (
MONTH ( 'Calendar'[Date] ) = 2
&& DAY ( 'Calendar'[Date] ) = 29
)
)
)
In my calendar there are all dates from the beginning of the year the minimum date of hiring to the end date of the analyzed year. In practice, for each frame move I received a new line with the date of the beginning of the event, the date of completion of the event, the date of hiring (not tied to the dates of the event, only to the current period of work in the company) and the date of dismissal (if not dismissed, date = 01.01 .2099).
b) to count rolling year:
CALCULATE (
[Average Headcount];
FILTER (
ALL ( 'Calendar' );
'Calendar'[SequentialDayNumber]
> MAX ( 'Calendar'[SequentialDayNumber] ) - 365
&& 'Calendar'[SequentialDayNumber] <= MAX ( 'Calendar'[SequentialDayNumber] )
)
)
3) To count numbers of dismissed for each day and sum for the period:
SUMX (
ADDCOLUMNS (
VALUES ( 'Calendar'[Date] );
"Dismissed"; SUMX (
FILTER (
'Base';
'Base'[Start date of the period] <= 'Calendar'[Date]
&& 'Base'[End date of the period] >= 'Calendar'[Date]
&& 'Base'[Date of dismissal] = 'Calendar'[Date]
);
'Base'[Headcount]
)
);
[Dismissed]
)
4) To count number of dismissed rolling year:
CALCULATE (
[Number of Dismissed];
FILTER (
ALL ( 'Calendar' );
'Calendar'[SequentialDayNumber]
> MAX ( 'Calendar'[SequentialDayNumber] ) - 365
&& 'Calendar'[SequentialDayNumber] <= MAX ( 'Calendar'[SequentialDayNumber] )
)
)
5) To count Turnover rolling year:
IFERROR (
[Number of Dismissed rolling year] / [Average Headcount rolling year];
0
)