How to get a calculated column for 7 day average with different categories

[JoeWilde]

Hi there,

I'm trying to create a calculated column that can calculated the 7 day average based on each category available. 

Example of dataset below (for just two days but this would be for multiple years worth of data):

Region	Date	Category	Value
England	04/08/2023	Cases	1
London	04/08/2023	Cases	4
Borough	04/08/2023	Cases	2
England	04/08/2023	Different cases	3
London	04/08/2023	Different cases	1
Borough	04/08/2023	Different cases	2
England	03/08/2023	Cases	5
London	03/08/2023	Cases	6
Borough	03/08/2023	Cases	2
England	03/08/2023	Different cases	1
London	03/08/2023	Different cases	4
Borough	03/08/2023	Different cases	6

I would then like there to be a calculated column that works out the running average for the last 7 days for EACH category (in my actual data there will be more than just two different types of category) and geography. 

I.e. In this example a calculated column would have for 04/08/2023:

Geography	Category	Weekly Average
England	Cases	3
London	Cases	5
Borough	Cases	2
England	Different cases	2
London	Different cases	2.5
Borough	Different cases	4

I hope this is all understandable and thanks!

[Mkarwa-123]

@JoeWilde 
You create a new week column either by week number or week date and after that group by your required data to get expected output.

Did I answer your question? Mark my post as a solution!

[JoeWilde]

Thanks for your reply. So as I wanted it to display on each day what the previous 7 days average was I don't believe this would work unless I've misunderstood your solution. 

So for example, I would want 07/08 to show an average from 01/08 to 07/08 and for 08/08 I would want 02/08 to 08/08's average. 

Also just for clarity, I would be presenting this data into a line chart showing each different geography's 7 day average.

[v-shex-msft]

Hi @JoeWilde,

Here is the calculated column formula that claatue the rolling average based on current row date, you can try it if helps:

formula =
CALCULATE (
    AVERAGE ( Table1[Value] ),
    FILTER (
        Table1,
        Table1[Category] = EARLIER ( Table1[Category] )
            && AND (
                [Date]
                    >= EARLIER ( Table1[Date] ) - 7,
                [Date] <= EARLIER ( Table1[Date] )
            )
    )
)

Regards,

Xiaoxin Sheng