Hey, I am working with two tables, related on the below Date1 column. I want to ultimately get a rolling 7 day average (represented as a line) on the Line and Stacked Column. This line needs to be grouped by week (ideally the graph would show the starting date of the week represented). I am able to get a rolling average if I don't try to group each date into a week (see below screenshot) but I can't quite seem to get a 7 day rolling average and also have the bars grouped by week. Screenshot Data Table:IDZip CodeDate1Date2AgeRaceEthnicitySexStatusIn1280273/11/20203/11/202039WhiteNot Hispanic or LatinoMaleRD 3280253/11/20203/16/202047UnknownNot Hispanic or LatinoFemaleRD 4280273/13/20203/18/202048WhiteNot Hispanic or LatinoFemaleRD Date Table:Date2/23/2020 0:002/24/2020 0:002/25/2020 0:002/26/2020 0:002/27/2020 0:002/28/2020 0:002/29/2020 0:003/1/2020 0:003/2/2020 0:003/3/2020 0:003/4/2020 0:003/5/2020 0:003/6/2020 0:003/7/2020 0:003/8/2020 0:003/9/2020 0:003/10/2020 0:003/11/2020 0:003/12/2020 0:003/13/2020 0:003/14/2020 0:003/15/2020 0:003/16/2020 0:003/17/2020 0:003/18/2020 0:003/19/2020 0:003/20/2020 0:003/21/2020 0:003/22/2020 0:003/23/2020 0:003/24/2020 0:003/25/2020 0:003/26/2020 0:003/27/2020 0:003/28/2020 0:003/29/2020 0:003/30/2020 0:003/31/2020 0:00

Hi, Anonymous Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end. Table: Calendar(a calculated table): Calendar = CALENDAR(DATE(2019,1,1),DATE(2020,12,31)) You may create calculated columns and a measure as below. Calculated column: Weeknum = WEEKNUM('Calendar'[Date]) Startofweek = CALCULATE( MAX('Calendar'[Date]), FILTER( ALL('Calendar'), 'Calendar'[Date]<=EARLIER('Calendar'[Date])&& WEEKDAY('Calendar'[Date])=1 ) ) Measure: Result = var _result = CALCULATE( AVERAGE('Table'[Value]), FILTER( ALL('Calendar'), 'Calendar'[Date]>=MIN('Calendar'[Date])-7&& 'Calendar'[Date]<MIN('Calendar'[Date]) ) ) return IF( ISBLANK(_result), AVERAGE('Table'[Value]), _result ) Result: Best Regards Allan If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous

Not applicable

6 years ago

Solved

Rolling Average Grouped by Week

Hey,

I am working with two tables, related on the below Date1 column. I want to ultimately get a rolling 7 day average (represented as a line) on the Line and Stacked Column. This line needs to be grouped by week (ideally the graph would show the starting date of the week represented). I am able to get a rolling average if I don't try to group each date into a week (see below screenshot) but I can't quite seem to get a 7 day rolling average and also have the bars grouped by week.

Screenshot

Data Table:

Zip Code

Date1

Date2

Age

Race

Ethnicity

Sex

Status

28027

3/11/2020

White

Not Hispanic or Latino

Male

28025

3/11/2020

3/16/2020

Unknown

Not Hispanic or Latino

Female

28027

3/13/2020

3/18/2020

White

Not Hispanic or Latino

Female

Date Table:

Date

2/23/2020 0:00

2/24/2020 0:00

2/25/2020 0:00

2/26/2020 0:00

2/27/2020 0:00

2/28/2020 0:00

2/29/2020 0:00

3/1/2020 0:00

3/2/2020 0:00

3/3/2020 0:00

3/4/2020 0:00

3/5/2020 0:00

3/6/2020 0:00

3/7/2020 0:00

3/8/2020 0:00

3/9/2020 0:00

3/10/2020 0:00

3/11/2020 0:00

3/12/2020 0:00

3/13/2020 0:00

3/14/2020 0:00

3/15/2020 0:00

3/16/2020 0:00

3/17/2020 0:00

3/18/2020 0:00

3/19/2020 0:00

3/20/2020 0:00

3/21/2020 0:00

3/22/2020 0:00

3/23/2020 0:00

3/24/2020 0:00

3/25/2020 0:00

3/26/2020 0:00

3/27/2020 0:00

3/28/2020 0:00

3/29/2020 0:00

3/30/2020 0:00

3/31/2020 0:00

Need Help

v-alq-msft

6 years ago

Hi, Anonymous

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Calendar(a calculated table):

Calendar = CALENDAR(DATE(2019,1,1),DATE(2020,12,31))

You may create calculated columns and a measure as below.

Calculated column:
Weeknum = WEEKNUM('Calendar'[Date])
Startofweek = 
CALCULATE(
    MAX('Calendar'[Date]),
    FILTER(
        ALL('Calendar'),
        'Calendar'[Date]<=EARLIER('Calendar'[Date])&&
        WEEKDAY('Calendar'[Date])=1
    )
)

Measure:
Result = 
var _result = 
CALCULATE(
    AVERAGE('Table'[Value]),
    FILTER(
       ALL('Calendar'),
       'Calendar'[Date]>=MIN('Calendar'[Date])-7&&
       'Calendar'[Date]<MIN('Calendar'[Date])
    )
)
return
IF(
    ISBLANK(_result),
    AVERAGE('Table'[Value]),
    _result
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Rolling Average Grouped by Week.pbix71 KB

3 Replies

amitchandak
Super User
6 years ago
Anonymous , not very clear with your data. I hope you have created a Date table with the week.

And have a rolling formula like

Rolling 7 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-7,Day))

For Week refer :https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

v-alq-msft

Community Support

6 years ago

Hi, Anonymous

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Calendar(a calculated table):

Calendar = CALENDAR(DATE(2019,1,1),DATE(2020,12,31))

You may create calculated columns and a measure as below.

Calculated column:
Weeknum = WEEKNUM('Calendar'[Date])
Startofweek = 
CALCULATE(
    MAX('Calendar'[Date]),
    FILTER(
        ALL('Calendar'),
        'Calendar'[Date]<=EARLIER('Calendar'[Date])&&
        WEEKDAY('Calendar'[Date])=1
    )
)

Measure:
Result = 
var _result = 
CALCULATE(
    AVERAGE('Table'[Value]),
    FILTER(
       ALL('Calendar'),
       'Calendar'[Date]>=MIN('Calendar'[Date])-7&&
       'Calendar'[Date]<MIN('Calendar'[Date])
    )
)
return
IF(
    ISBLANK(_result),
    AVERAGE('Table'[Value]),
    _result
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Rolling Average Grouped by Week.pbix71 KB

v-alq-msft
Community Support
6 years ago
Hi, Anonymous

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Rolling Average Grouped by Week

3 Replies

Related content

Grouped Rolling Average

Rolling 12M Average measure

Rolling accumulation

3 Day Rolling Average - DAX

Conditional Rolling Average