Calculate Max of n instances before date

[seanmcc]

Hi all,

I am struggling to get the Max value from the previous 10 entries, irrespective of dates.

My Measure looks like the following;

SSHLIP L Max (Last 10) =

VAR recentdate = LASTNONBLANK(Readiness[Date],LASTNONBLANKVALUE(Readiness[SSLIHP - Peak Vertical Force - Left],MAX(Readiness[Date])))

VAR enddate = recentdate - 10

RETURN

CALCULATE(MAX(Readiness[SSLIHP - Peak Vertical Force - Left]), Readiness[Date] < recentdate && Readiness[Date] >= enddate)

Any help is greatly appreciated.

Thanks

Sean

[littlemojopuppy]

Can you provide some sample data to work with?

[amitchandak]

@seanmcc , Not very clear. You need data for last 10 dates or entries

Try one of the two

measure =

var _max = maxx(allselected(Readiness), Readiness[Date])
var _min = _max -10
return
calculate(count(Readiness[Date]), filter( Readiness, Readiness[Date] >=_min && Readiness[Date] <=_max))

or

measure =

var _max = maxx(allselected(Readiness), Readiness[SSLIHP - Peak Vertical Force - Left])
var _min = _max -10
return
calculate(count(Readiness[Date]), filter( Readiness, Readiness[SSLIHP - Peak Vertical Force - Left] >=_min && Readiness[SSLIHP - Peak Vertical Force - Left] <=_max))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[seanmcc]

Hi  @littlemojopuppy and @amitchandak  , apologies if not clear. See an example of the 'Readiness' data table I am working with;

Name	Date	Test	Value
Player 1	28/12/2020	SSLIHP - Peak Vertical Force - Left	405
Player 1	22/12/2020	SSLIHP - Peak Vertical Force - Left	459
Player 1	18/12/2020	SSLIHP - Peak Vertical Force - Left	404
Player 1	17/12/2020	SSLIHP - Peak Vertical Force - Left	475
Player 1	14/12/2020	SSLIHP - Peak Vertical Force - Left	363
Player 1	10/12/2020	SSLIHP - Peak Vertical Force - Left	422
Player 1	07/12/2020	SSLIHP - Peak Vertical Force - Left	395
Player 1	03/12/2020	SSLIHP - Peak Vertical Force - Left	407
Player 1	30/11/2020	SSLIHP - Peak Vertical Force - Left	481
Player 1	26/11/2020	SSLIHP - Peak Vertical Force - Left	386
Player 1	17/11/2020	SSLIHP - Peak Vertical Force - Left	479
Player 1	11/11/2020	SSLIHP - Peak Vertical Force - Left	372
Player 1	01/01/2021	KangaTech - Abduction - Left	312.1
Player 1	28/12/2020	KangaTech - Abduction - Left	264.8
Player 1	22/12/2020	KangaTech - Abduction - Left	322.3
Player 1	18/12/2020	KangaTech - Abduction - Left	317.1
Player 1	17/12/2020	KangaTech - Abduction - Left	318
Player 1	14/12/2020	KangaTech - Abduction - Left	291.2
Player 1	10/12/2020	KangaTech - Abduction - Left	303.4
Player 1	07/12/2020	KangaTech - Abduction - Left	322.3
Player 1	03/12/2020	KangaTech - Abduction - Left	262.2
Player 1	30/11/2020	KangaTech - Abduction - Left	346.6
Player 1	26/11/2020	KangaTech - Abduction - Left	349.8
Player 1	17/11/2020	KangaTech - Abduction - Left	318.8

I want to create a measure that will show me the Max score from the latest 10. For example;

My most recent date for [SSLIHP - Peak Vertical Force - Left] is 28/12/2020.

I want to look at the latest ten dates from 26/11/2020 - 28/12/2020.

The expected result is 481.

I hope that is clearer?

Thanks

Sean