Forum Discussion
Rolling average with multiple tanks
My data looks like this:
Tank | Date | Level |
Tank 1 | 8/3/2024 | 1 |
Tank 1 | 8/4/2024 | 2 |
Tank 1 | 8/5/2024 | 1 |
Tank 2 | 8/3/2024 | 10 |
Tank 2 | 8/4/2024 | 15 |
Tank 2 | 8/5/2024 | 12 |
I need to create a “table” in Power BI that looks like this:
Tank | Date | Level | Rolling 7 day average |
Tank 1 | 8/3/2024 | 1 | Averages the level from 7/27 to 8/3 just for Tank 1 |
Tank 1 | 8/4/2024 | 2 | Averages the level from 7/28 to 8/4 just for Tank 1 |
Tank 1 | 8/5/2024 | 1 | Averages the level from 7/29 to 8/5 just for Tank 1 |
Tank 2 | 8/3/2024 | 10 | Averages the level from 7/27 to 8/3 just for Tank 2 |
Tank 2 | 8/4/2024 | 15 | Averages the level from 7/28 to 8/4 just for Tank 2 |
Tank 2 | 8/5/2024 | 12 | Averages the level from 7/29 to 8/5 just for Tank 2 |
Hi jeffwash666 ,
I create a table as you mentioned.
Then I create a calculated column and here is the DAX code.
Rolling7DayAvg = CALCULATE ( AVERAGE ( 'Table'[Level] ), DATESINPERIOD ( 'Table'[Date], 'Table'[Date], -7, DAY ), ALLEXCEPT ( 'Table', 'Table'[Tank] ) )Finally you will get what you want.
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi jeffwash666 ,
I create a table as you mentioned.
Then I create a calculated column and here is the DAX code.
Rolling7DayAvg = CALCULATE ( AVERAGE ( 'Table'[Level] ), DATESINPERIOD ( 'Table'[Date], 'Table'[Date], -7, DAY ), ALLEXCEPT ( 'Table', 'Table'[Tank] ) )Finally you will get what you want.
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Brilliant, thanks so much!!
Create a CC :
Rolling 7 Day Average = VAR CurrentTank = 'MyData'[Tank] VAR CurrentDate = 'MyData'[Date] RETURN CALCULATE( AVERAGE('MyData'[Level]), FILTER( 'MyData', 'MyData'[Tank] = CurrentTank && 'MyData'[Date] <= CurrentDate && 'MyData'[Date] >= CurrentDate - 6 ) )
