Forum Discussion
Rolling Average Help!
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
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
1 Reply
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