Rolling average with adjustable time frame

[BryantSturn]

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.

[lbendlin]

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-...

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/1447...

[BryantSturn]

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!

[lbendlin]

See attached

[BryantSturn]

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

[lbendlin]

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

[BryantSturn]

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.

[lbendlin]

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

see attached.

[BryantSturn]

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.