Create rolling average by day of week and then sum at week level

[jags8386]

I have following table. 

Record Date	Item Count
2/25/2024	504
2/26/2024	516
2/27/2024	617
2/28/2024	700
2/29/2024	987
3/1/2024	727
....	....
....	...
...	...
3/15/2024	893
3/16/2024	891
3/17/2024	779
3/18/2024	810
3/19/2024	648
3/20/2024	784

From this I am calculating rolling  week average for same day of week. For example for date 3/24/2024, it should be rolling average of 2/25/24, 3/3/24, 3/10/24 and 3/17/24

2/25/2024	504
3/3/2024	757
3/10/2024	630
3/17/2024	779
Rolling avg for 3/24	667.5

I am able to calculate this for individual dates. But My total is 731.8 in below example instead of 2069.8

3/24/2024	667.5
3/25/2024	670.5
3/26/2024	731.8
Total	2069.8

Last4weekAvg = 

var SelectDate = MAX(DATE_TABLE[D_DATE])
var v1 = 
    CALCULATE(
        [Total_Items], 
        FILTER(
           all( DATE_TABLE), 
             DATE_TABLE[D_DATE] >= SelectDate - 28
             && WEEKDAY( DATE_TABLE[D_DATE], 1 ) = WEEKDAY( SelectDate, 1 )
                &&  DATE_TABLE[D_DATE] < SelectDate
        )
    )
return v1

[Anonymous · Accepted solution]

Hi @audreygerred ,

Thanks for your sharing.

Hi @jags8386 ,

I have created a simple sample, please refer to my pbix file to see if it helps you.

Create 2 columns.

weekday = WEEKDAY('Table'[date],2)

weeknum = WEEKNUM('Table'[date],11)

Then create a measure.

Measure = var _1=SELECTEDVALUE(datetable[Date])
var _1weeknum=WEEKNUM(_1,11)
var _1weekday=WEEKDAY(_1,2)
RETURN
CALCULATE(AVERAGE('Table'[value]),FILTER(ALL('Table'),'Table'[weeknum]<=_1weeknum&&'Table'[weeknum]>=_1weeknum-3&&'Table'[weekday]=_1weekday))

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[jags8386]

AverageItemCount = AVERAGEX(ALL('YourTable'), SUM('Table'[item count]))

Is above mesure caculate rolling 4 week average for given weekday? for example, monday will be rolling average of last 4 mondays.

[audreygerred]

Hi! You'll want to use iterator functions to tackle this. Here is a blog article I wrote that explains how these work in more detail: http://powerbiwithme.com/2023/11/01/the-iterator-edition/

First, for the average measure:

AverageItemCount = AVERAGEX(ALL('YourTable'), SUM('Table'[item count])) <-- if you have a measure already created for the sum of ItemCount you can use that here instead.

Now, the measure that will show you the average per row, but a sum for your subtotals and totals:

SumOfAverageItemCount = SUMX(VALUES('YourTable'), [AverageItemCountPerRow])

If you want subtotals at a weekly level, just add a week number to your visual and you can have your subtotals show for the week level 🙂