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
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.
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
Vehicle Last month mileage