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]

What is the expected output based on the sample you have provided?

[Hakim-A]

Basically what I need to have is the last 2 results/tables on the right.

Here I represented the different calculation step to retrieve average per vehicle and per month.

Thank you

[sunny_talwar]

From 555508 and 566864 for Vehicle A in November, how are you getting 12167 in the second table?

[Hakim-A]

30*(566864-555508)/(28)

28 represents the days difference between 1/11 and 29/11

[sunny_talwar · Accepted solution]

Try this

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

[Hakim-A]

Thank you I adapted the formula a little bit and it is working perfectly

[sunny_talwar]

Awesome

[Hakim-A]

Another question linked to this exercise

What if I want to display the latest value of this aggregation in a table

Of course last month can be different for each vehicle

Example

Vehicle   Last month mileage

A               7135

B               8086

Thank you

[Hakim-A]

The idea is to have the avg monthly mileage per vehicle but also the latest month mileage to see if there is any deviation