Hi Pigment Community!
I am trying to create a year over year moving average but can’t seem to figure it out. I was wondering if any of you had some suggestions for me.
The issue I am trying to solve is that I have a conversion rate calculated in a metric. Using that metric I would like to calculate the difference in moving average between the current year and previous year. Formulaically it would be: (Moving Average Current Year) - (Moving Average Previous Year) * 100. In addition I want to incorporate my switchover date so that my metric will continuously update itself as new information comes in.
Currently I have this formula in mind, but it doesn’t work. Perhaps there’s a better way, or my formula could be tweaked.
MOVINGAVERAGE('[ % ] Opportunity to Customer', 3) - MOVINGAVERAGE('[ % ] Opportunity to Customer', 3, 0, '[ % ] Opportunity to Customer'.DATE(YEAR(EDATE('Switchover date', -12)), MONTH(EDATE('Switchover date', -12)), 1)
What I envision is: ((Nov 2023, Oct 2023, Sep 2023) / 3) - ((Nov 2022, Oct 2022, Sep 2022) / 3)) * 100
I would greatly appreciate your insights and suggestions on refining the formula or exploring alternative approaches to achieve the desired year-over-year moving average calculation. If you have any expertise in this area or have encountered a similar challenge, your guidance would be invaluable.
Best answer by BrunoRodriguezView original