March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet 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
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!
@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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
116 | |
83 | |
76 | |
62 | |
58 |
User | Count |
---|---|
141 | |
122 | |
105 | |
94 | |
90 |