Rolling 12 month measure

[Anonymous]

Hi All

I have a complex question I'll try to articulate properly.

I'm trying to create a 2 rolling 12 month measure that sounds mathmatically incorrect but is industry standard so I must stick to it.

The measures I need are a rolling 12 month attrition % and a 12 month rolling retention measure. and here are the calculation rules.

Monthly Attrition % = No people resigned in the month / average( start of month headcount : end of month headcount)

I have successfully created the follwoing 3 measures above

1. No of people resigned in a month

2. Start of month headcount

3. End of month headcount

And from there I have successfully created a measure called monthly attrition %.

Now for the rolling 12 month measure I just need the sum of the 12 monthly attrition %. so for example of the monthly attrition was 4% each month the rolling 12 month attrition would be 48% and the rolling 12 month retention result is 52% (1-48%).

Please note the headcount numbers vary month to month and have peaks and troughs.

When I try and do the 12 month measure it tries do the monthly attrition measure over 12 months which is not what I need. I just need the monthly attrition reuslts summed over 12 months for attrition and from there do a (1-attrition) to get the retention results. In excel this is a simple exercise...

	monthly attrition	Rolling 12 month attrition
May-15	3.81%
Jun-15	3.84%
Jul-15	4.76%
Aug-15	5.31%
Sep-15	5.06%
Oct-15	5.12%
Nov-15	3.59%
Dec-15	3.56%
Jan-16	4.07%
Feb-16	4.93%
Mar-16	3.95%
Apr-16	4.60%	52.60%
May-16	4.23%	53.02%
Jun-16	3.67%	52.85%
Jul-16	4.26%	52.35%
Aug-16	5.39%	52.43%
Sep-16	4.43%	51.80%
Oct-16	3.08%	49.76%
Nov-16	3.73%	49.90%
Dec-16	3.24%	49.58%
Jan-17	3.23%	48.74%
Feb-17	3.74%	47.55%
Mar-17	4.93%	48.53%
Apr-17	3.29%	47.22%

[vanessafvg]

@Anonymous can you post the measures you have created?  please also post what you expect it to do as opposed to only  what its doing to get an idea of what you want.

[Anonymous]

Sorry I know its confusing when you cannot see all the files/data

Step 1 was was ti identify the attrition by date that measure which is below

Attrition by date = CALCULATE(COUNTROWS('common vwDimEmployee'),filter('common vwDimEmployee','common vwDimEmployee'[TerminationDate]<= LASTDATE('common vwDimDate'[Date])&&'common vwDimEmployee'[TerminationDate]>=firstdate('common vwDimDate'[Date])))

Step 2 was to get a start of month headcount and end of month headcount. These measures are below

Active SOM HC by Month = CALCULATE(COUNTROWS('common DimEmployee'),filter('common DimEmployee','common DimEmployee'[StartDate]<= STARTOFMONTH('common vwDimDate'[Date])&&'common DimEmployee'[End Date Adjusted]>=STARTOFMONTH('common vwDimDate'[Date])))

Active EOM HC by Month = CALCULATE(COUNTROWS('common DimEmployee'),filter('common DimEmployee','common DimEmployee'[StartDate]<= ENDOFMONTH('common vwDimDate'[Date])&&'common DimEmployee'[End Date Adjusted]>=ENDOFMONTH('common vwDimDate'[Date])))

Step 3 was to work out the monthly attrition percentage based on our business rules which were

Attrition in the month divided by the average start of month and end of month headcounts. The measure is below

Monthly Attrition % Position = [Attrition by date]/(([Active SOM HC by Month]+[Active EOM HC by Month])/2)

Please note all the above measures are working exactly as we want them to work.

Step 4 is to get a rolling 12 month Attrition percentage. The business rulle for this is to simply sum the Monthly Attrition $ postition measure for 12 months. We want the measure dynamic meaning always looking at a rolling 12 months based on the month you want the score. for example the rolling 12 months result for May 17 would simply sum the monthly percentages for Jun16 to May 17. So again just take the monthly percentage for each month and sum them (dont try and do a weighted average based on chenges in headcounts etcbecause those are not the business rules).

So in the example below the rolling 12 month attrition % would be 50.59% for May 17.

	Monthly Attrition % Position
Jun-16	4.50%
Jul-16	3.64%
Aug-16	5.00%
Sep-16	2.98%
Oct-16	6.72%
Nov-16	5.43%
Dec-16	4.75%
Jan-17	1.19%
Feb-17	3.64%
Mar-17	1.52%
Apr-17	6.78%
May-17	4.44%

Step 5 is 12 month rolling retention %. This needs to be (1-"12 month rolling attrition%") so in the example above it would equal 49.41% for May 17

I hope that makes sense.

In my attempts I can reconcile the number Power BI is producing. Its using the monthly attrition % position measure and applying it for a 12 month period. I dont want that I want it to grab each monthly result as thay are and sum the monthly results.

[v-jiascu-msft · Accepted solution]

@Anonymous

Hi,

In your scenario, the measure can’t evaluate through the whole year. Maybe you can try this formula. It’s just a thought. Maybe you need to adjust the details. You need Year column and Month column in your table 'common vwDimDate' in order to group by your data by Year and Month. Please have a try.

Measure =
VAR sd =
    LASTDATE ( DATEADD ( 'common vwDimDate'[Date], -1, YEAR ) )
RETURN
    CALCULATE (
        SUMX (
            SUMMARIZE (
                'common DimEmployee',
                'common vwDimDate'[Year],
                'common vwDimDate'[Month],
                "MA%P", [Monthly Attrition % Position] )
            ),
            [MA%P]
        ),
        FILTER (
            ALL ( 'common vwDimDate' ),
            'common vwDimDate'[Date] > sd
                && 'common vwDimDate'[Date] <= MAX ( 'common vwDimDate'[Date] )
        )
    )

If you can post the fields of the tables and the relationships, that would be a great help.

Best Regards!

Dale

[Anonymous]

Hi v-jiascu-msft 

I got it to work thank you very much. Just reshuffled around the brackets.

Here is the finished formula

Rolling 12mth Attrition % = VAR sd = LASTDATE ( DATEADD ( 'common vwDimDate'[Date], -1, YEAR ) )RETURN CALCULATE (SUMX (SUMMARIZE ('common vwDimEmployee', 'common vwDimDate'[YearKey],'common vwDimDate'[Month],"AnnualAttrition", [Monthly Attrition % Position] ), [Monthly Attrition % Position] ), FILTER ( ALL ( 'common vwDimDate' ), 'common vwDimDate'[Date] > sd && 'common vwDimDate'[Date] <= MAX ( 'common vwDimDate'[Date] )))

Thanks again...

[v-jiascu-msft]

@Anonymous

My pleasure. I am so glad you get it to work. 

Best Regards!

Dale

[Anonymous]

This formula is above my skill set. I wish I could screenshot where is breaking down.

So within the formual you know how it gives a jagged red underline where the formuala is not working? Well I have pasted the formula below and highlighted in red bold where its not working.

Measure = VAR sd = LASTDATE ( DATEADD ( 'common vwDimDate'[Date], -1, YEAR ) )RETURN CALCULATE (SUMX (SUMMARIZE ('common vwDimEmployee', 'common vwDimDate'[YearKey],'common vwDimDate'[Month],"Monthly Attrition % Position", [Monthly Attrition % Position] )), [Monthly Attrition % Position] ) , FILTER ( ALL ( 'common vwDimDate' ), 'common vwDimDate'[Date] > sd && 'common vwDimDate'[Date] <= MAX ( 'common vwDimDate'[Date] )))

By the way all the table/column references are correct.