Calculating monthly mileage - dealing with outliers and empty values

[gingerclaire]

I have a mileage spreadsheet and in it I am using the following measure to work out how much mileage they have done in each month:

Mileage since last month = 'Car report data'[Current mileage:]-'Car report data'[Prev Reading]

To get Prev Reading I am using the following calculated column:

Prev Reading =

MAXX(

TOPN(

1,

FILTER(

'Car report data',

'Car report data'[Car reg] = EARLIER( 'Car report data'[Car reg] )

&& 'Car report data'[Start time] < EARLIER( 'Car report data'[Start time] )

),

'Car report data'[Start time]

),

'Car report data'[Current mileage:]

)

The problem is that when there is no previous reading, then this throws out a negative number.

Would I better saying if there is no previous reading it should = 0? How would i do this?

The issue is that some of the drivers have had several different cars, but I want to see their miles driven each month by driver (regardless of car) so that I can then forecast their mileage needs (to ensure their contract allows for this).

Any help gratefully received.

[daXtreme]

Instead of trying to twist and turn your data to suit your complex calculations, transform the data to make your calculations easy. This is the first part of data modeling art. So, if the granularity of your calendar is 'day', just make sure that your fact table does not store cumulative mileages but mileages per day. Then everything will become very easy. Obviously, if the calendar granularity should be 'month,' store mileages per month.

[gingerclaire]

Thank you - the data is actually from an MS Form where the drivers enter their current mileage - so I need to convert it somehow.

[Anonymous]

Hi @gingerclaire ,

In order to provide you a suitable solution, could you please provide some sample data in the table "Car report data" (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

How to upload PBI in Community

Best Regards