Average of last 4 by time

[c_latham]

Hi All,

I am attempting to work out a rolling average based on the last 4 visits a car had calculated by a visit time. Data as shown below:

VisitID	CarName	VisitDate	DurationMins
1281	Mercedes	16/02/2020 13:00:00.000	37
9827	BMW	16/02/2020 12:47:29.000	23
9848	BMW	13/02/2020 09:32:34.000	24
3923	Mercedes	12/02/2020 06:14:32.000	65
2819	Mercedes	11/02/2020 08:32:50.000	12
1209	Ford	10/02/2020 02:02:08.000	68
1987	Mercedes	13/02/2020 18:00:07.000	24
2403	Mercedes	11/02/2020 19:32:03.000	16

I would want an extra column on the table with an average like the below:

VisitID	CarName	VisitDate	DurationMins	4 Average
1281	Mercedes	16/02/2020 13:00:00.000	37	34.5
9827	BMW	16/02/2020 12:47:29.000	23
9848	BMW	13/02/2020 09:32:34.000	24
3923	Mercedes	12/02/2020 06:14:32.000	65
2819	Mercedes	11/02/2020 08:32:50.000	12
1209	Ford	10/02/2020 02:02:08.000	68
1987	Mercedes	13/02/2020 18:00:07.000	24
2403	Mercedes	11/02/2020 19:32:03.000	16

I am attempting to use something like:
=avg(aggr(FirstsortedValue(DurationMins,VisitDate),CarName))

But I cannot get it to fit my requirement of last 4 visits.

Any help would be great!

[TimvB]

I do not understand the final measure that you need and why you use FirstSortedValue(), so I cannot provide a working expression. However, if you would like to calculate an average over the last four rows the function Below() will do the job.

Hope it helps!

[c_latham]

I was just attempting to get something to work...

Thanks to your pointer I then have gone for:

=Aggr(RangeAvg(Below(([DurationMins]),0,4)), 0, [CarName], VisitDate)

This gives me no results 😣