Weekly Rolling average for already calculated measure

[lukeSDM]

Hello,

I am trying to create a weekly rolling average for my measure calculated attendance : 

"([AlL Present]/[Measure])*100"

which is :

"ALL Present = CALCULATE(COUNT(AttendanceOvertime[Statistical meaning]),AttendanceOvertime[Statistical meaning] IN {"Present", "Approved Educational Activity"})"

and 

"Measure = 

CALCULATE (

    COUNT ( AttendanceOvertime[Statistical meaning] ),

    NOT AttendanceOvertime[Statistical meaning] IN { "Attendance not required" }

)"

Whenever I have researched and tried it does not work - please see graph below.

So I would likw to have the calculated attendance fro each week and then the rolling average as each week goes by.

Any help is greatly appreciated,

Thanks,

Luke

[v-zhenbw-msft]

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.

[lukeSDM]

I tried this before but it didnt seem to work.

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

[littlemojopuppy · Accepted solution]

Below are the formulas for all the measures I'm using.  Without seeing what you're expected result based on the data provided, it's kind of difficult to figure out what, if anything, is wrong.  Could you please provide some details?

ALL Present:=CALCULATE(
	COUNTROWS(AttendanceOvertime),
	AttendanceOvertime[Statistical meaning] IN {"Present", "Approved Educational Activity"}
)


ALL Present Running Total:=CALCULATE(
		[ALL Present],
		FILTER(
			ALL('Calendar'),
			'Calendar'[Date] <= MAX('Calendar'[Date])
		)
	)


Measure:=CALCULATE (
	COUNT ( AttendanceOvertime[Statistical meaning] ),
	AttendanceOvertime[Statistical meaning] IN { "Attendance not required" }
)


Measure Running Total:=CALCULATE(
		[Measure],
		FILTER(
			ALL('Calendar'),
			'Calendar'[Date] <= MAX('Calendar'[Date])
		)
	)


All Present Running Average:=DIVIDE(
		[ALL Present Running Total],
		[Measure Running Total],
		BLANK()
	)

[lukeSDM]

Hi @littlemojopuppy 

Here is some example data.

No.	Year taught in Code	Mark	Mark date	AM/PM	Statistical meaning
410	9	/	07/09/2020	AM	Present
410	9	\	07/09/2020	PM	Present
410	9	/	08/09/2020	AM	Present
410	9	\	08/09/2020	PM	Present
410	9	/	09/09/2020	AM	Present
410	9	\	09/09/2020	PM	Present
410	9	X	14/09/2020	AM	Attendance not required
410	9	X	14/09/2020	PM	Attendance not required
410	9	X	17/09/2020	AM	Attendance not required
410	9	X	17/09/2020	PM	Attendance not required
410	9	/	10/09/2020	AM	Present
410	9	\	10/09/2020	PM	Present
410	9	X	11/09/2020	AM	Attendance not required
410	9	X	11/09/2020	PM	Attendance not required
410	9	/	22/09/2020	AM	Present
410	9	\	22/09/2020	PM	Present
410	9	X	15/09/2020	AM	Attendance not required
410	9	X	15/09/2020	PM	Attendance not required
410	9	X	16/09/2020	AM	Attendance not required
410	9	X	16/09/2020	PM	Attendance not required
410	9	/	25/09/2020	AM	Present
410	9	\	25/09/2020	PM	Present
410	9	X	18/09/2020	AM	Attendance not required
410	9	X	18/09/2020	PM	Attendance not required
410	9	/	21/09/2020	AM	Present
410	9	\	21/09/2020	PM	Present
410	9	/	30/09/2020	AM	Present
410	9	\	30/09/2020	PM	Present
410	9	/	23/09/2020	AM	Present
410	9	\	23/09/2020	PM	Present
410	9	/	24/09/2020	AM	Present
410	9	\	24/09/2020	PM	Present
410	9	/	05/10/2020	AM	Present
410	9	\	05/10/2020	PM	Present
410	9	/	28/09/2020	AM	Present
410	9	\	28/09/2020	PM	Present
410	9	/	29/09/2020	AM	Present
410	9	\	29/09/2020	PM	Present
410	9	/	08/10/2020	AM	Present
410	9	\	08/10/2020	PM	Present
410	9	/	01/10/2020	AM	Present
410	9	\	01/10/2020	PM	Present
410	9	/	02/10/2020	AM	Present
410	9	\	02/10/2020	PM	Present
410	9	/	13/10/2020	AM	Present
410	9	\	13/10/2020	PM	Present
410	9	/	06/10/2020	AM	Present
410	9	\	06/10/2020	PM	Present
410	9	/	07/10/2020	AM	Present
410	9	\	07/10/2020	PM	Present
410	9	/	16/10/2020	AM	Present
410	9	\	16/10/2020	PM	Present
410	9	/	09/10/2020	AM	Present
410	9	\	09/10/2020	PM	Present
410	9	/	12/10/2020	AM	Present
410	9	\	12/10/2020	PM	Present
410	9	/	21/10/2020	AM	Present
410	9	\	21/10/2020	PM	Present
410	9	/	14/10/2020	AM	Present
410	9	\	14/10/2020	PM	Present
410	9	/	15/10/2020	AM	Present
410	9	\	15/10/2020	PM	Present
410	9	/	03/11/2020	AM	Present

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!

[littlemojopuppy]

Is this what you're looking for?

All Present Running Average:=CALCULATE(
	DIVIDE(
		[ALL Present],
		[Measure],
		BLANK()
	),
	FILTER(
		ALL('Calendar'),
		'Calendar'[Date] <= MAX('Calendar'[Date])
	)
)

[v-zhenbw-msft]

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.

[lukeSDM]

@v-zhenbw-msft 

This is similar to what I am trying to achieve however, I am calculating non numerical values so I already have a calculated measure in place as mentioned in my first post.

So although this is similar to what i would like to achieve , it does not work in theory as my calculations are not as straight forward as that.

[littlemojopuppy]

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?

[lukeSDM]

This is what my data looks like, does this help.

[littlemojopuppy]

Would be easier if you could provide an actual (anonymous) data to work with instead of a screen shot

[lukeSDM]

My aplogies @littlemojopuppy ,

I will get this to you shortly.

[littlemojopuppy]

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

[lukeSDM]

I am looking to get a cumulative figure, so after 2 weeks I would get the average, of the first and second week, then after 3 weeks it would average of the 1st 2nd and 3rd week.

Does that make sense at will your previous dax work?

Many thanks

[amitchandak]

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