Forum Discussion
Calculated table or column with a rolling average
- Anonymous5 years ago
Hi ChristyQV ,
Try using this formula to calculate the average value.
Column = CALCULATE(AVERAGE('FactPumpageDaily'[RunHours]),FILTER(ALLEXCEPT('FactPumpageDaily','FactPumpageDaily'[WellCode]),'FactPumpageDaily'[Date]>EARLIER('FactPumpageDaily'[Date])-3&&'FactPumpageDaily'[Date]<=EARLIER('FactPumpageDaily'[Date])))Best Regards,
Jay
Hi ChristyQV ,
If "Desired column- 3 Day Running Avg" is the result you want, I just can't figure out how did you get it by "RunHours". Please correct me If I misunderstand your meaning.
Best regards,
Jay
The running average is the average of the hours from the selected date and the preceding two days. It works as a measure just fine as defined below. (My sample data just has enough info to check for 5/1/2021.) Ultimately, I am trying to count the number of times the average is >= 15. The Calculatetable in my question is just my first step in that process. After getting the CalculateTable/summarize to work, my intent is to wrap it in a CountRows, somthing like below
3daycount =
COUNTROWS (
FILTER (
SUMMARIZE (
FactPumpageDaily,
FactPumpageDaily[Date],
FactPumpageDaily[WellCode],
"3DAY",
CALCULATE (
AVERAGEX ( FactPumpageDaily, FactPumpageDaily[RunHours] ),
DATESINPERIOD ( DimDates[Date], MAX ( DimDates[Date] ), -3, DAY )
)
),
[3DAY] >= 15
)
)
When I use this, I always just get the count for the record date (which is 1 or 0), even when I add an All(DimDates[Date]) to the calculate to change the date context.
- Anonymous5 years agoNot applicable
Hi ChristyQV ,
Try using this formula to calculate the average value.
Column = CALCULATE(AVERAGE('FactPumpageDaily'[RunHours]),FILTER(ALLEXCEPT('FactPumpageDaily','FactPumpageDaily'[WellCode]),'FactPumpageDaily'[Date]>EARLIER('FactPumpageDaily'[Date])-3&&'FactPumpageDaily'[Date]<=EARLIER('FactPumpageDaily'[Date])))Best Regards,
Jay
- ChristyQV5 years agoFrequent Visitor
Works like a charm and is easier than the path that I was on. Thanks so much.