Average of day for each individual and then average of the last 10 days excluding today

[LS-PScience]

Hi guys

I am trying to average the mRSI from each day for each individual (GB and LS), and then show the average for the last 10 sessions excluding the current session. Expected results for GB are below (less than 10 sessions are shown but I'd like it to average the last 10 sessions (one session = 3 events) if there was enough data.

The average by day is easy enough, its the next part thats tricky...

In my model I have used the following to calculate average by day for each individual:

AVERAGEX(VALUES('Smarta - Hawkin CMJ'[Date]), [AveragemRSI])

Please see sample data attached

Date	Name	mRSI		Average by day		Average of last 10 sessions (excluding today)
27/07/2022	GB	0.68		0.69		0.54	"=AVERAGE(E5,E8,E11,E14,E17,E20)"
27/07/2022	GB	0.71
27/07/2022	GB	0.68
20/07/2022	GB	0.52		0.48		0.55	"=AVERAGE(E8,E11,E14,E17,E20)"
20/07/2022	GB	0.28
20/07/2022	GB	0.62
16/06/2022	GB	0.66		0.59		0.55	"=AVERAGE(E11,E14,E17,E20)"
16/06/2022	GB	0.71
16/06/2022	GB	0.41
13/04/2022	GB	0.53		0.54		0.55	"=AVERAGE(E14,E17,E20)"
13/04/2022	GB	0.55
13/04/2022	GB	0.55
07/04/2022	GB	0.57		0.52		0.56	"=AVERAGE(E17,E20)"
07/04/2022	GB	0.42
07/04/2022	GB	0.58
02/02/2022	GB	0.44		0.53		0.58
02/02/2022	GB	0.58
02/02/2022	GB	0.58
27/01/2022	GB	0.56		0.58
27/01/2022	GB	0.57
27/01/2022	GB	0.6
27/07/2022	LS	0.65
27/07/2022	LS	0.43
27/07/2022	LS	0.46
20/07/2022	LS	0.7
20/07/2022	LS	0.6
20/07/2022	LS	0.8
16/06/2022	LS	0.68
16/06/2022	LS	0.55
16/06/2022	LS	0.47
13/04/2022	LS	0.59
13/04/2022	LS	0.6
13/04/2022	LS	0.78
07/04/2022	LS	0.77
07/04/2022	LS	0.65
07/04/2022	LS	0.56
02/02/2022	LS	0.43
02/02/2022	LS	0.34
02/02/2022	LS	0.65
27/01/2022	LS	0.88
27/01/2022	LS	0.76
27/01/2022	LS	0.8

Thanks

[v-jianboli-msft · Accepted solution]

Hi @LS-PScience ,

Please try:

First create a rank column:

Rank = RANKX(FILTER('Smarta - Hawkin CMJ',[Name]=EARLIER('Smarta - Hawkin CMJ'[Name])),[Date],,DESC,Dense)

Output:

Then create a new measure:

Average_ =

CALCULATE (

    AVERAGE ( 'Smarta - Hawkin CMJ'[mRSI] ),

    FILTER (

        ALL ( 'Smarta - Hawkin CMJ' ),

        [Name] = MAX ( 'Smarta - Hawkin CMJ'[Name] )

            && [Rank] > MAX ( 'Smarta - Hawkin CMJ'[Rank] )

            && [Rank]

                <= MAX ( 'Smarta - Hawkin CMJ'[Rank] ) + 10

    )

)

Show items with no data in the table visual:

Output:

Best Regards,

Jianbo Li

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

[amitchandak]

@LS-PScience , Based on what I got

AVERAGEX(VALUES('Smarta - Hawkin CMJ'[Name]),calculate( Average ( 'Smarta - Hawkin CMJ'[RSI]) ) )