Rolling average with multiple tanks

[jeffwash666]

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

[Anonymous · Accepted solution]

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.

[jeffwash666]

Brilliant,  thanks so much!!

[AmiraBedh]

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