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.
Average Headcount :=
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:
Average Headcount 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:
Number of Dismissed :=
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:
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:
Staff turnover rolling year :=
IFERROR (
[Number of Dismissed rolling year] / [Average Headcount rolling year];
0
)
