cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Regular Visitor

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

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!

1 ACCEPTED SOLUTION
Community Champion

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!

2 REPLIES 2
Resolver I

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.

Best Regards,
Henry

Community Champion

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!

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors