Weekly Rolling average for already calculated measure

lukeSDM , Not very clear.

Have Rank columnin you week and date table

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense) // you can use YYYYWW - year Week

Then you can have rolling measure like

Last 8 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-8 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

Other measures

This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))

Question...are you looking for week to week or a rolling seven day period?

Rolling seven days would be calculated like this.  Modify as appropriate for your data model...

CALCULATE(
	[ALL Present],
	DATESINPERIOD(
		'Calendar'[Date],
		LASTDATE('Calendar'[Date]),
		-7,
		DAY
	)
)

The DAX formula I provided won't work for that.  Sounds like more of a running average of weeks...assuming weeks run Sunday to Saturday?

Can you provide some sample data to play with?

Hi lukeSDM ,

Do you want to calculate the average like this?

For example, in week 2, calculate the average of week 1 and week 2.

If yes, we can create a measure to meet your requirement.

Measure = 
var _sum = CALCULATE(SUM('Table'[Values]),FILTER(ALLSELECTED('Date'),'Date'[week]<=MAX('Date'[week])))
return
DIVIDE(_sum,MAX('Date'[week]))

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

Hi littlemojopuppy 

Here is some example data.

I created a date table with min and max values in this table and then added week number and months etc.

Please refer to my opening post for the calculated measures I use which I use to calculate percentages

Then I added the week number

If you would like more data you could just copy and paste and use a different number at the beginning and tweak it.

I hope this helps and makes sense.

Thank you for your help so far!

I tried this before but it didnt seem to work.

Rolling Average = AVERAGEX(FILTER(ALLSELECTED( Dates ), Dates[Date] <= MAX(Dates[Date] ) ), [Calculated attendance])

Hi lukeSDM ,

Do you want to calculate the average of accumulative, then divide the accumulative of count of Statistic?

For example, in your sample, week 37, 38, 39, the total is 18, and the value in week 39 is 18 / 3 = 6.

If yes, you can refer this Measure. (You need to create a week column in your table.)

Measure = 
VAR _accum =
    CALCULATE (
        [ALL Present],
        FILTER (
            ALLSELECTED ( AttendanceOvertime ),
            AttendanceOvertime[Week] <= MAX ( AttendanceOvertime[Week] )
        )
    )
VAR _count_week =
    CALCULATE (
        DISTINCTCOUNT ( AttendanceOvertime[Week] ),
        FILTER (
            ALLSELECTED ( AttendanceOvertime ),
            AttendanceOvertime[Week] <= MAX ( AttendanceOvertime[Week] )
        )
    )
RETURN
    DIVIDE (
        DIVIDE ( _accum, _count_week ),
        CALCULATE (
            COUNT ( AttendanceOvertime[Statistical meaning] ),
            FILTER (
                ALLSELECTED ( AttendanceOvertime ),
                AttendanceOvertime[Week] <= MAX ( AttendanceOvertime[Week] )
            )
        )
    )

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.