Hi All,

I have a measure that is producing the correct figures in a table, but wildly different and inconsistent results in a clustered column and line chart.

All of the filters are the same and whatever I try doesn’t fix the problem, despite weeks of sleepless nights and going round in circles!

Any help would be greatly appreciated.

Here is the measure:

Actual Utilised Days = SUMX(VALUES(BookingVehicles[VehicleId]), [Total Days Utilised])

and here is Total Days Utilised:

Total Days Utilised =

```
var sameVehicle = COUNTROWS (
FILTER (
VALUES ( BookingVehicles[BookingId] ),
CALCULATE ( COUNT ( BookingVehicles[VehicleId] ) > DISTINCTCOUNT ( BookingVehicles[VehicleId] ) )
)
)
return {
IF(NOT(sameVehicle),
SUMX(
VALUES(BookingVehicles[BookingId]), [New No of Utilised Days]
),
DIVIDE(SUMX(
VALUES(BookingVehicles[BookingId]), [New No of Utilised Days]
), 2 )
)
}
```

And lastly, here is New No of Utilised Days:

New No of Utilised Days =

```
var bookingID = MAX(BookingVehicles[BookingId])
var vehicleSwap = COUNTROWS(FILTER(BookingVehicles, BookingVehicles[BookingId] = bookingID)) >1
var noVehicleSwap = COUNTROWS(FILTER(BookingVehicles, BookingVehicles[BookingId] = bookingID)) = 1
var moreThanTwoVehicles = COUNTROWS(FILTER(BookingVehicles, BookingVehicles[BookingId] = bookingID)) > 2
var endTime = IF(noVehicleSwap, MAX(BookingVehicles[Effective End Datetime]), IF(MAX(BookingVehicles[Actual Delivery Time]) <MAX( BookingVehicles[Effective End Datetime]) && MAX(HireStatus[Hire Status Name]) = "Finalised", MAX(BookingVehicles[Actual Delivery Time]), MAX(BookingVehicles[Effective End Datetime])))
var startTime = IF(MAX(BookingDates[ChargeFromDateTime]) > MAX(BookingVehicles[Effective Start Datetime]), MAX(BookingDates[ChargeFromDateTime]), MAX(BookingVehicles[Effective Start Datetime]))
var TotalMinutesHired = DATEDIFF(startTime, endTime , MINUTE)
var totalDaysHired = DATEDIFF(startTime, endTime, DAY)
var endGracetime = MAX(BookingVehicles[Effective End Time])
var startGraceTime = MAX(BookingVehicles[Effective Start Time])
var Grace = IF(endGracetime < startGraceTime, 0, MOD(TotalMinutesHired,1440)) // Capture early returns
var GraceDiff = Grace - 1438 // To capture whole days from 00.01 to 23.59 that are not grace time
var AllocatedGraceDays = MAX(BookingDates[GracePeriod]) // From vehicle swaps etc
var diffMinutes = MAX(BookingVehicles[DeliveryTime]) < MAX(BookingVehicles[Effective Start Time])
var notLastVehicle = MAX(BookingVehicles[Effective Start Date]) = MAX(BookingDates[ChargeFromDate])
var Days = MAX(BookingVehicles[Effective End Datetime]) - MAX(BookingVehicles[Effective Start Datetime]) - AllocatedGraceDays
var LastDayofDateRange = MAX(Dates[Date])
var FirstDayofDateRange = MIN(Dates[Date])
var result =
SUMX(
FILTER(BookingVehicles, NOT(ISBLANK(BookingVehicles[VehicleId]))),
var firstDayOfUtilisation = startTime
var lastDayOfUtilisation = endTime
return
IF(
ISBLANK(firstDayOfUtilisation) || firstDayOfUtilisation > lastDayOfUtilisation,
0,
COUNTROWS(
INTERSECT(
CALENDAR(firstDayOfUtilisation, lastDayOfUtilisation),
VALUES('Dates'[Date])
)
)
)
)
var allowBlank = NOT(ISBLANK([No of InFleet Days]))
var isBlank = AND(allowBlank, ISBLANK(result))
return IF(isBlank, 0, IF([lastDayOfUtilisation] > LastDayofDateRange,result,
IF([firstDayOfUtilisation] >= FirstDayofDateRange && [lastDayOfUtilisation] <= LastDayofDateRange, result - 1,
IF(TotalMinutesHired< 1440, result,
IF(GraceDiff = 0, result,
IF(vehicleSwap && notLastVehicle && moreThanTwoVehicles, result +1,
IF(vehicleSwap && notLastVehicle, result,
IF(diffMinutes && vehicleSwap, result,
IF(Grace >59, result +1, result
))))))))
```

)

Once again, thanks in advance for any help