Power BI DAX rolling average of last 10 data row entries (excluding data from current date)

[PS_123456]

Hi guys

Im looking to calculate a rolling average of the last 10 data entries excluding todays date

Example data below

Table _1

Date	mRSI	Expected measure result on that day
2/08/22	.6	(.7+.3+.8+.3+.3+.4+.2+.3+.1+.8) / 10
2/08/22	.5
2/08/22	.4
1/07/22	.7	(.3+.3+.4+.2+.3+.1+.8+.9+.8) / 9
1/07/22	.3
1/07/22	.8
1/06/22	.3	(.2+.3+.1+.8+.9+.8) / 6
1/06/22	.3
1/06/22	.4
1/05/22	.2
1/05/22	.3
1/05/22	.1
1/04/22	.8
1/04/22	.9
1/04/22	.8

Hope this makes sense. Obviously if there was more data and it wasn't showing the start of the table then each days measure would be dividing by 10.

Thanks!

[Henrykong_]

Hi @PS_123456 ,

Whether the advice given by @littlemojopuppy  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

[littlemojopuppy · Accepted solution]

Hi @PS_123456 

Try this measure:

Average Last 10 mRSI:=
VAR	LastTenDataPoints =
	TOPN(
		10,
		SampleData,
		[Date],
		DESC
	)
VAR	DataPointTotal =
	SUMX(
		LastTenDataPoints,
		[mRSI]
	)
RETURN

DIVIDE(
	DataPointTotal,
	10,
	BLANK()
)

Measures are in Power Pivot in the attached workbook.

For the sample data you provided, this is the output

It's including 11 data points because there's a tie (working backwards) to 1/5/2022 and "If there is a tie, in Order_By values, at the N-th row of the table, then all tied rows are returned...."  But if you have additional data points (maybe time?) you'd be able to break it.

Hope this helps!