Aggregated average based on firstsortedvalue - Help

[Hakim-A]

Hello everyone,

Please help me I am struggling with calculation of aggregated average.

I have data like this representing the mileage at different point of time for different vehicles.

Vehicle Date-Time  Mileage

See data enclosed.

I would like to have one calculation representing the average mileage for our population based on first and last date of the month for each vehicle.

For example : 5th June vehicle A has 10.000 km 25th June it has 15.000 km it drove 5000 km in 20 days so avg mileage per month is 30*5000/20 = 7500 km/month  (if the vehicle has data between these 2 dates we ignore them, we just want to know the first and last date of each month per vehicle)

8th of June vehicle B has 8.000 km and 15th of June it has 10.000km so average is 30*2000/7=8571 km/month

So for both vehicle average mileage in June is 8035 (7500+8571/2)

This 8035 I would like to calculate it in a single formula.

Could you please help me  and apply the same logic to my data ?

In advance thanks for your help

[sunny_talwar]

May be this

FirstSortedValue(Aggr((FirstSortedValue(VALUE, -READOUT_DATETIME) - FirstSortedValue(VALUE, READOUT_DATETIME)) * 30/(Max(DATE) - Min(DATE)), Vehicle, MonthYear), -Aggr(Max(READOUT_DATETIME), Vehicle, MonthYear))

[Hakim-A]

Thank you for this reactive support

Much appreciated