Labour Turnover Rate

[Anonymous]

Hi

I have month start snapshot data going back to 1st April 2021 for employees in post.  Anonymised examples below......

You can see that one head (EE_Ref) can have more than one role (Role_Ref), and that each head and role combination has a row entry against each month start snapshot (reporting date), as long as they were employed at least part way through the previous month.

All dates in the table have an INactive relationship with the date field in a date table:

I have created the following measure to provide the distinct number of heads at snapshot date.

Current Staff =

CALCULATE(DISTINCTCOUNT(PBI_EOM_Snapshots[EE_Ref]),

USERELATIONSHIP(PBI_EOM_Snapshots[ReportingDate], 'Dates'[Date]),

FILTER(VALUES('PBI_EOM_Snapshots'[JoinDate]), PBI_EOM_Snapshots[JoinDate] <= MAX(Dates[Date])),

FILTER(VALUES('PBI_EOM_Snapshots'[LeaveDate]), OR(PBI_EOM_Snapshots[LeaveDate] >= MIN(Dates[Date]), ISBLANK(PBI_EOM_Snapshots[LeaveDate]))))

Resulting Matrix:

The next step is to calculate the average of these values dating back to the start of the financial year each time.  For example, the Jun-21 figure for Company O would be (1459+1438+1391)/3, and the Aug-21 figure for Company C would be (241+242+243+247+247)/5.

This is the part that I have hit the proverbial brick wall.  I have seen similar examples in previous threads but nothing that deals with the summing of month end snapshot distinct counts.  Any help would be greatly appreciated.  Many thanks.

[Greg_Deckler]

@Anonymous This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

If that doesn't help, probably need sample data as text.

[Anonymous]

@Greg_Deckler

Hi Greg, link to file below.  Same data numbers, just anonymised.

sample_pb 

I think having read your blog article that this is along the lines of what I am needing but have struggled to apply the logic to my data.

As mentioned previously, I am stuck with creating an additional measure that cumulatively sums the measure used to create the Active Staff at Snapshot matrix values, leading to something like this......

I would then look to divide each value by FINMONTHNUMBER to obtain an average FYTD.  Thanks in advance.

[YukiK]

If you have an index column per month-year then you can probably do something like this:

CALCULATE(
SUM(yourMeasure),
FILTER(
ALLSELECTED(monthyear),
ISONORAFTER(monthyear, MAX(monthyear), DESC)
)
)

/
SELECTEDVALUE( yourIndex )

Hope this helps!

[Anonymous]

@YukiK 

Hi, apologies if I am being a bit thick (!) but the sum function requires a column rather than a measure.  What am I doing wrong?  I get where you are coming from re an Index and was planning to use financial month number in my date table as the denominator in the calculation.  Thanks.