Doing a moving average using window functions in Analyzer
I have this salesforce lead historical dataset (which tracks Lead stage changes) and I want to show a month-on-month moving average of the number of days it takes a lead to convert to the next stage (e.g. 30 days from Stage 1 to Stage 2) with Stage as series/dimension in my card.
The dashboard/cards that I'm creating is filterable by multiple fields so my best option is to use the window function in Analyzer (so the card values dynamically changes based on filter selection). My main problem is that there are valid duplicate rows in my dataset as 1 lead can be tagged to 1 or more teams.
To illustrate, below is an example of my dataset where Lead-01 is tagged to 2 Teams. I tried using the formula below to get the moving average but the issue here is it includes the duplicate 30 days from Lead-01.
AVG(AVG(`No. of Days to Convert`)) OVER (PARTITION BY `Stage` ORDER BY `Month`)
My desired output is for the month of March (based on Stage Exit Date), the average no. of days to convert for Suspect stage should be 25 days (average of 30 & 20). In the same month, when I filter Team=A, avg. days to convert is 25 and for Team=B, it should be 30.
I can't seem to get the output that I want with my current dataset. Is there another way to do this? Thanks!