topic Re: Issue with rolling average based off item type in DAX Commands and Tips

Issue with rolling average based off item type

JohnFabric — Fri, 10 Jan 2025 01:31:47 GMT

Hello, I'm looking to create a rolling 90 day/3 month average of LeadTime over the course of a year. This is what my data looks like

The issue with this is I have three different WorkItemType values: "Task", "Feature" and "PBI" so even though all the data is in a single table, I need three different rolling averages, one per type.

The DAX I have so far is for a single type, Feature. I assume I will need 3 measurements in total, one for each.

Rolling Average = VAR LastTransactionDate = MAX('Query1'[CompletedDateSK]) VAR AverageDay = 90 VAR PeriodInVisual = FILTER( ALL( 'Query1'[CompletedDateSK] ), AND( 'Query1'[CompletedDateSK] > LastTransactionDate - AverageDay, 'Query1'[CompletedDateSK] <= LastTransactionDate ) ) VAR WorkItemType = FILTER( ALL( 'Query1'[WorkItemType] ), AND( 'Query1'[WorkItemType] = "Feature", 'Query1'[WorkItemType] = "Feature" ) ) VAR OutPut = CALCULATE( AVERAGEX( 'Query1', [LeadTimeDays] ), PeriodInVisual, WorkItemType ) RETURN OutPut

Which feels like it can be improved. I'm also not sure it is calculating the right values either, since the rolling average is being calculcated as the same as the month average

How can I calculate rolling average correctly given my data?

Thanks!

Re: Issue with rolling average based off item type

adudani — Fri, 10 Jan 2025 02:03:23 GMT

hi JohnFabric

This reference shows how to use concat to check if the moving average is calculated as expected.
How to Calculate Moving Averages in Power BI - The Ultimate Guide

you can modify the "Dates in period" part of the DAX code to put in the required MA period.

Lastly, if you apply a visual level filter using Workitem Type on the matrix, you could have 3 visuals filtered with the same DAX code.

hope this helps. let me know if this resolves the issue.

if it doesn't, kindly upload a sample input (masking sensitive information) in a usable format such as table, csv, excel etc. and a sample output.

Re: Issue with rolling average based off item type

JohnFabric — Fri, 10 Jan 2025 22:27:52 GMT

Thanks, I was able to figure this out based on the video. I needed to change my formula. Each time I listed

Query1'[CompletedDateSK]

I needed to change it to

Query1'[CompletedDateSK].[Date]

That correctly calculated a 90 day average. Then I created 3 measurements and got them all displayed correctly