Moving Average KPI YTD and MTD

cztmm — Tue, 31 Jan 2023 13:43:38 GMT

Hi all,

I am trying to work on a visual to track the change in our task completion durations through our reporting year and month and compare it to a target. We want to visualise the average duration when we complete a task and compare this with the movement throughout the year as historical average movement. The target for comparison differs based upon whether the task follows Process A (45) or Process B (90).

I previously calculated this using structured references in Excel offline, however these are unsupported in Office365. Using Excel to store this data is a bottleneck dependency which needs eliminating for wider service functionality. Therefore, I need to migrate this calculation for the visual into PowerBI or PowerQuery, either using DAX or M. I'm facing challenges when trying to calculate the moving averages and in getting them to display correctly in the visual. Due to company policies, I am unable to use custom visuals.

How can I accurately calculate the moving averages and display in a KPI visual with historical movement?
How can I filter the measures to select a certain time frame without needing to alter the date range each month/year?
How can I prevent the KPI measure from displaying (Blank) when there is not a task completed today?

Example data below:

ID Number (A)	Process (B)	Date Assigned (C)	Date of Completion (D)	Time to Complete (E)	Year Average (F)	Month of Completion (G)	Month Average (H)	Month Indicator (I)
1	A	26-Oct-22	28-Nov-22	33	33	11	33	False
2	A	26-Oct-22	13-Dec-22	48	40.5	12	48	True
3	A	27-Jul-22	03-Aug-22	7	29.33333333	8	7	False
4	A	27-Jul-22	14-Sep-22	49	34.25	9	49	False
5	A	17-Aug-22	30-Aug-22	13	30	8	10	False
6	A	17-Aug-22	26-Sep-22	40	31.66666667	9	44.5	False
7	B	20-Oct-22	03-Nov-22	14	14	11	14	False
8	B	24-Nov-22	06-Jan-23	43	28.5	1	43	False
9	B	21-Dec-22	03-Jan-23	13	23.33333333	1	28	False
10	B	23-Nov-22	06-Dec-22	13	20.75	12	13	True

Time to Complete is now calculated in PowerQuery (Date of Completion - Date Assigned)
Year Average was calculated in Excel using AVERAGEIF. It should be year to date (i.e. average of cumulative sum). Our reporting year is 1st October 2022 until 30th September 2023. The formula I used to calculate this in Excel was:

=AVERAGEIF($B$2:$B10,$B10,$E$2:$E10)

Month of Completion was used to identify the Month in Month Average
Month Average should be the average of the cumulative sum in the Month to Date (e.g. if today is January, I want to have cumulative average for ongoing assignments coming in during January but also be able to look back at older months). The formula I used to calculate this in Excel was:

=AVERAGEIFS($E$2:$E10,$B$2:$B10,$B10,$G$2:$G10,$G10)

Month Indicator was used to control the MTD KPI visuals to display last month (e.g. December if today is January) - this is probably unnecessary now.

I think that the method I used to calculate averages previously was inaccurate, as it was dependant on date-order being correct. In the example data above, task 9 would not be included in calculating the average for January when assessing task 8.

The KPI visuals I was driving with Excel-based calculations looked like this:

Any support you can offer would be greatly appreciated!

topic Moving Average KPI YTD and MTD in DAX Commands and Tips

Moving Average KPI YTD and MTD