I have a mileage report which looks at how much mileage my drivers have done and how much their lease contract allows over the full term of the contract.
I have set up a gauge visual which shows their current mileage and has a max of their full contract mileage over the course of the lease.
I want to be able to set the 'target' of the gauge to show where they are in their contract in terms of time. i.e. Tom has a four year contract and has a lease for 10,000 miles per year. So when he is two years into his contract, I would like his target to sit at 20,000. So that I can see if he is over or underusing his mileage.
I guess it would have to be a measure relating the most recent submission date of their car report to the date their lease started? bu ti have no idea how to do that 😞
I have a measure for current mileage.
My data looks like this:
Vehicle lease spreadsheet (I also have a 'date' table and a 'car report' table containing their mileage readings and submission date etc):
| Length of lease | Mileage allocation per annum | Full contract mileage | Reg No | Name | Start of lease |
| 4 | 10000 | 40000 | xxx xxx | Tom | 01.02.20 |
| 3 | 12000 | 36000 | yyy yyy | Dave | 05.06.21 |
| 4 | 20000 | 80000 | rrr rrr | Sally | 08.06.1999 |
| 2 | 14000 | 28000 | ddd ddd | Claire | 01.08.2021 |
