I have a column chart measure average cycle time for tasks by month, an average of the total number of days in progress for tasks in a given month. It currently only shows 6 months, April to September. I am trying to add a line to show the 30 day rolling average, so I want this calculation to take into account the previous 30 from before the given month, and the line will show that across the column chart for each month. Essentially I want to show a rolling average of the average. I've tried several different calculations using Averagex and I keep running into a similar issue where the numbers just don't make sense. For instance, the rolling average for April doesn't match the average (The average is ~80 while the rolling average is ~60), which doesn't make sense because there's no data earlier than April.
This is the main formula I've been using:
SMA measure = AVERAGEX(DATESBETWEEN(Tasks[Completed].[Date], MIN(Tasks[Completed].[Date])-30, MAX(Tasks[Completed].[Date])),CALCULATE(AVERAGE('Task days in progress'[Days In Progress])))
Interestingly, when I remove the wrapping CALCULATE function, the formula matches the monthly average which I'm already showing on the column chart. It stops factoring in the -30. With CALCULATE function in place, I can add or subtract numbers from the MIN or MAX of the date range and the numbers do shift, albeit still not making sense as a rolling average. Any thoughts on why CALCULATE is giving me a different result or how I can adjust my formula?
