Rolling Average Help!

[Anonymous]

Hi everyone!

I have been banging my head against the wall with this one for quite some time.  I have found some calculations in the forums that solve similar problems but I cannot for the life of me implement them in my dataset.  

My data involves contracts and analyzing those contracts for a renewal percentage.

So - the data:

Max End Date formula:

MaxDate = CALCULATE(MAX(Contracts[End Date]),ALLEXCEPT(Contracts,Contracts[Serial]))

Renewed going forward? = IF([MaxDate]>Contract[End Date],1,0)

Expirations = 1 for all rows as it represents an expiring contract

Renewal Percentage = [Renewed Going Forward?]/[Expirations]

SERIAL	START DATE	END DATE	MAX END DATE	RENEWED GOING FORWARD?	EXPIRATIONS	RENEWAL PERCENTAGE
ABC	1/1/2016	1/1/2017	1/1/2019	1	1	100%
ABC	1/1/2017	1/1/2018	1/1/2019	1	1	100%
ABC	1/1/2018	1/1/2019	1/1/2019	0	1	0%
BCD	1/1/2016	1/1/2017	1/1/2017	0	1	0%
CDE	1/1/2016	1/1/2017	1/1/2018	1	1	100%
CDE	1/1/2017	1/1/2018	1/1/2018	0	1	0%
DEF	1/1/2016	1/1/2017	1/1/2017	1	1	100%

All of these thrown into a matrix gets me the following:

What I need to add is a rolling average and because of measures I used earlier some of the tips I've found don't apply to what I'm working on.  

Thanks in advance for any advice!!

Cheers - Tom

[v-caliao-msft · Accepted solution]

Hi @Anonymous,

To achieve rolling average measure, you need to get the total value, and then use the total value divided by month number. Here is the sample DAX for you referebce.
3 Month Moving Sum Units Sold] =
CALCULATE([Units Sold],
          DATESINPERIOD(Calendar[Date],
                        LASTDATE(Calendar[Date]),-3, Month
                       )
         )

[3 Month Moving Avg Corrected]=

[3 Month Moving Sum Units Sold] /
CALCULATE(DISTINCTCOUNT(Calendar[Year Month]),
          DATESINPERIOD(Calendar[Date],
                        LASTDATE(Calendar[Date]),-3,Month
                       )
         )

Reference
https://powerpivotpro.com/2013/07/moving-averages-sums-etc/
http://www.sqlbi.com/articles/rolling-12-months-average-in-dax/

Regards,

Charlie Liao