Employee attrition - moving average of a percentage Measure

[Anonymous]

Hi Everyone,

I have a report called worker data extract which is an appended long query, containing headcount-related demographic information and in a field called [Date] the date when the report was pulled which is the last day of each month. I have a related table ("Dim_Dates") in which all the days of the year are paired with a given label called [Month] in the order like the following:

01/07/2016 - 31/07/2016 as "F17 P01"

01/08/2016 - 31/08/2016 as "F17 P02"

...

01/10/2017 - 31/10/2017 as "F18 P04"

I also have a table called "Fact_Leavers" that contains demographic data about employees who left. (That also contains a date column which is related to my Dim_Date table.

Total attrition % is equal to the sum of people left the company divided by the average headcount in the last 12 months.  I have a bunch of filters added to my report which can alter filter context, but mostly I want to add Month so one may always see attrition based on the average headcount and leavers of the selected month + the 11 month's data (total leavers / avg headcount) before.

I created a terrible formula that works but which I need to alter every month when new dataset is added to my file and obviously I can not use Month filter for this.

How can I alter it to be a moving average of percentage measure?

Attrition_Total =
DIVIDE (
COUNTROWS ( Fact_Leavers ),
(
CALCULATE (
COUNTROWS ( 'Fact_Worker Data Extract' ),
Dim_Date[Month] = "F18 P04"
)
+ CALCULATE (
COUNTROWS ( 'Fact_Worker Data Extract' ),
Dim_Date[Month] = "F18 P03"
)
+ CALCULATE (
COUNTROWS ( 'Fact_Worker Data Extract' ),
Dim_Date[Month] = "F18 P02"
)
+ CALCULATE (
COUNTROWS ( 'Fact_Worker Data Extract' ),
Dim_Date[Month] = "F18 P01"
)
+ CALCULATE (
COUNTROWS ( 'Fact_Worker Data Extract' ),
Dim_Date[Month] = "F17 P12"
)
+ CALCULATE (
COUNTROWS ( 'Fact_Worker Data Extract' ),
Dim_Date[Month] = "F17 P11"
)
+ CALCULATE (
COUNTROWS ( 'Fact_Worker Data Extract' ),
Dim_Date[Month] = "F17 P10"
)
+ CALCULATE (
COUNTROWS ( 'Fact_Worker Data Extract' ),
Dim_Date[Month] = "F17 P09"
)
+ CALCULATE (
COUNTROWS ( 'Fact_Worker Data Extract' ),
Dim_Date[Month] = "F17 P08"
)
+ CALCULATE (
COUNTROWS ( 'Fact_Worker Data Extract' ),
Dim_Date[Month] = "F17 P07"
)
+ CALCULATE (
COUNTROWS ( 'Fact_Worker Data Extract' ),
Dim_Date[Month] = "F17 P06"
)
+ CALCULATE (
COUNTROWS ( 'Fact_Worker Data Extract' ),
Dim_Date[Month] = "F17 P05"
)
)
/ 12)

[v-jiascu-msft]

Hi @Anonymous,

Try this formula please.

Attrition_Total =
DIVIDE (
    COUNTROWS ( Fact_Leavers ),
    CALCULATE (
        COUNTROWS ( Fact_Leavers ),
        DATESINPERIOD ( 'Dim_Date'[Date], LASTDATE ( 'Dim_Date'[Date] ), -12, MONTH )
    )
        / 12
)

The date field in the visual should be 'Dim_Date'[Date].

Best Regards!

Dale

[Anonymous]

Hi Dale,

Thank you for your support and sorry for the late response - the issue still persist.

It is not exactly that I was looking for.

On the date axis of the visual I would need to show my own Month values ("F17 P01", "F17 P02" etc....) as described above.

Is that possible?

Many thanks in advance for your response.

Best regards,

György

[v-jiascu-msft]

Hi György,

Could you please share a sample? Either you need to add a new column in your date table or you need a new table of the month values.

Best Regards,

Dale