topic Re: Rolling average with adjustable time frame in DAX Commands and Tips

Rolling average with adjustable time frame

BryantSturn — Mon, 01 Jul 2024 20:44:20 GMT

I'm having issues creating a measure to calculate the average over the past X days for a specific account. In the initial Data I have multiple rows per day for several accounts with a specific value recorded for each account, each day. The data is over a year and I need to come up with an average amount looking back X days, then compare the current days value to the average amount over the adjustable period. to calculate the average amount I have a query that almost works, it just looks at every day and creates an average instead of averaging over the days in the spreadsheet (which excludes weekends and holidays). How would I be able to fix this issue? I need to create a matrix which will show every value and then count the amount of times the specific day breaks the specified average value.

AverageAmountOverXDays =

VAR SelectedDays = SELECTEDVALUE(DaysP arameter[Days Parameter])

RETURN

CALCULATE(

AVERAGE('Table'[Amount]),

DATESINPERIOD('Date'[Date], MAX('Date' [Date]), -SelectedDays, DAY)

)

I also have a reference calendar that contains only dates from the data and a count to rank them. I couldn't figure out how to take advantage of it though.

Re: Rolling average with adjustable time frame

lbendlin — Tue, 02 Jul 2024 01:39:21 GMT

Sounds like Numeric Parameters can handle this for you. How far back should X be?

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Rolling average with adjustable time frame

BryantSturn — Wed, 03 Jul 2024 00:58:52 GMT

For the attached file I would need a rolling average that only includes dates found in the data and is set by the parameter

RollingAverage.pbix

If you need anything else please let me know!

Re: Rolling average with adjustable time frame

lbendlin — Wed, 03 Jul 2024 01:18:36 GMT

See attached

Re: Rolling average with adjustable time frame

BryantSturn — Wed, 03 Jul 2024 02:44:08 GMT

I need the weekends to not count in the equation if possible, that was where I was having the most trouble. For example, if X equals 10 6/28/2024 should average the previous 10 existing figures to 6/14/2024. Weekend figures shouldn't be present

Re: Rolling average with adjustable time frame

lbendlin — Wed, 03 Jul 2024 11:39:58 GMT

What is your definition of weekend? What if there is a weekend day with data ? What if there is a weekday without data?

Re: Rolling average with adjustable time frame

BryantSturn — Wed, 03 Jul 2024 12:24:14 GMT

Sorry for the confusion but the data does not include weekends (Saturday or Sunday) or holidays. I would like to not count those, preferably by only counting which days are provided.

Re: Rolling average with adjustable time frame

lbendlin — Wed, 03 Jul 2024 12:50:45 GMT

Do you need the column or row totals? They would be much more complex to get accurate with your new rules.

see attached.

Re: Rolling average with adjustable time frame

BryantSturn — Wed, 03 Jul 2024 13:45:58 GMT

Thanks that's perfect. I do not need row or column totals. The only total I need is to compare the calculated average and the current day value, then count the amount of times the current day exceeds the calculated average (for each account, not summed across for the day). These are example numbers but we're creating a threshold for a lookback period to present. We need to compare the amount of threshold breaks with the size of the lookback period.