Previous three month average to compare to current month percent (rate)

Question & Answer
,
1

I am trying to produce a combo chart, the bars (columns) visualise the completion rate for our application MoM, however with the line I am trying to produce a line which tracks a three month average.
I am currently using the windowAvg function, however this captures the current month instead of the three previous (e.g. rolling average for Feb would be Feb, Jan, Dec). I cannot figure out how to change this or produce a three month rolling average which consists of the three previous months to the current month in question.
Example of calculated field I am using currently.
windowAvg
(
{Completion Rate},
[{customer_workflow_invited_at} ASC],
3,
0
)

Huge thank you in advance to any advice or suggestions.
Thank you,
Tom

2

not a smart solution but you may use lead to get the last 3 month, 2 month and 1month figure separately, then do a summation and divided by 3
(
lead(sum(Profit), [{Order Date} DESC], 3, ) +
lead(sum(Profit), [{Order Date} DESC], 2, ) +
lead(sum(Profit), [{Order Date} DESC], 1, )
) /3

2 Likes
3

Thank you this worked