rolling 12 month formula

System

This discussion was created from comments split from: Need Help with Formula for Rolling Metrics.

Tina Davis

I need help with a average rolling 12 month formula. I can get the 6 month rolling to work as stated below, however I am struggling with a 12 month. Any help would be appreciated.

=ROUND(AVG(COLLECT({Data days from requested to received}, {Data test fit received}, AND(@cell <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1, @cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 6, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 6, 1))), {Data design resource used}, "SDCM", {Data test fit approval status}, "Approved")), 1)

Paul Newcome

Have you tried just using a total of 365 days (or 183 in either direction)?

=ROUND(AVG(COLLECT({Data days from requested to received}, {Data test fit received}, AND(@cell <= TODAY(183), @cell >= TODAY(-183)), {Data design resource used}, "SDCM", {Data test fit approval status}, "Approved")), 1)

Vivien Chong

@Tina Davis I am thinking would it work if we replace the 6 with 12? I didn't test this formula yet.

=ROUND(AVG(COLLECT({Data days from requested to received}, {Data test fit received}, AND(@cell <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1, @cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 12, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 12, 1))), {Data design resource used}, "SDCM", {Data test fit approval status}, "Approved")), 1)

Paul Newcome

@Vivien Chong Maybe a +11 and -1.

If you go with 12 in either direction, you'll never get a valid month number.

@Tina Davis Taking a closer look at your formula, I realize you are basically going back 6 months. My formula would be for previous 6 months through next 6 months. If you just want to go back a year, that would look more like

DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), 1)

This would go inplace of the IFERROR/DATE/DATE you have set for the greater than or equal to portion in your formula.

Tina Davis

Thank you so much Paul, I was getting a divided by zero that I am looking at my data for accuracy now.