Forum Discussion

BryantSturn's avatar
BryantSturn
Regular Visitor
2 years ago

Rolling average with adjustable time frame

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 ...
BryantSturn's avatar
BryantSturn
Regular Visitor
2 years ago

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's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

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

  • BryantSturn's avatar
    BryantSturn
    Regular Visitor
    2 years ago

    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's avatar
      lbendlin
      Icon for Super User rankSuper User
      2 years ago

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

      see attached.

      • BryantSturn's avatar
        BryantSturn
        Regular Visitor
        2 years ago

        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.