Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
JoeWilde
Frequent Visitor

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

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

RegionDateCategoryValue
England04/08/2023Cases 1
London04/08/2023Cases4
Borough04/08/2023Cases2
England04/08/2023Different cases3
London04/08/2023Different cases1
Borough04/08/2023Different cases2

England

03/08/2023Cases5
London03/08/2023Cases6
Borough03/08/2023Cases2
England03/08/2023Different cases1
London03/08/2023Different cases4
Borough03/08/2023Different cases6

 

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:

GeographyCategoryWeekly Average
EnglandCases3
LondonCases5
BoroughCases2
EnglandDifferent cases2
LondonDifferent cases2.5
BoroughDifferent cases4

 

I hope this is all understandable and thanks!

3 REPLIES 3
Mkarwa-123
Resolver II
Resolver II

@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!

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.

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.