Calculating difference between numbers in different rows, different columns with filters - HELP!

[Daptoid]

Hi Power BI users.

I am hoping for some assistance as I am stumped for a solution to my problem.

I have a table of fleet car use. The table contains various different cars. Entries are usually made in successional order but sometimes backdated if someone incorrectly or forgets to enter the information at the correct time.

The table ‘Fleet Clean’  includes the columns ‘Vehicle’ listing a unique registration, ‘KMS at start’ (Kilometers at the start of the trip), ‘KMS at end’ and ‘DateTimeStart’  the date and time that the trip commenced.

I have been asked for an exceptions report that identifies gaps in the KMs between the end of the previous trip and the start of the next trip – in other words: unrecorded use of the vehicle.

Because it is all in the same table, I am finding it very difficult to filter for the vehicle, ordered by Date Time Start and the difference between the start KMs and End Kms which are in different columns and different rows.

I am hoping for something that looks like this

Vehicle	KMS at start	KMS at end	DateTimestart	Exception
abc123	100	105	8/01/2021  09:45:48 AM	0
cba321	50	55	8/01/2021  13:45:48 PM	0
abc123	125	140	8/01/2021  14:25:48 PM	20
cde234	60	65	8/01/2021  15:45:48 PM	0
abc123	140	149	8/01/2021  16:45:48 PM	0
cba321	55	60	9/01/2021  10:45:48 AM	0

Can anybody help me with this?

Thanks so much in advance for your time.

[Anonymous · Accepted solution]

Hi @Daptoid 

Try to build calculated columns to achieve your goal.

Firstly build a rank column.

Rank = RANKX(FILTER('Table','Table'[Vehicle]=EARLIER('Table'[Vehicle])),'Table'[ KMS at start],,ASC)

Then build Expection column as below.

Expection = 
VAR _Vehicle = 'Table'[ KMS at start]
VAR _NewVehicle =
    CALCULATE (
        SUM ( 'Table'[KMS at end] ),
        FILTER (
            'Table',
            'Table'[Vehicle] = EARLIER ( 'Table'[Vehicle] )
                && 'Table'[Rank]
                    = EARLIER ( 'Table'[Rank] ) - 1
        )
    )
RETURN
    IF ( 'Table'[Rank] = 1, 0, _Vehicle - _NewVehicle )

Result is as below.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Daptoid]

Hi @Anonymous This looks like quite a good option for my use case. I will be trying this as soon as I am next in the office. Thanks for your time and effort!

[Vera_33]

Hi @Daptoid 

Do you mean you have a table like above sample without the "Exception" coulmn which you want to have? You want to do it in M or DAX? Below is one in M, paste the code in Advanced Editor with a blank query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc9NCsMgEAXgq4RZBzIzzkh0lwMUupcsEpN79Sw9WfwpxVjoRkTe5+OFANseiQ2M8H4NhJguhJrOeUKaGJnSOzov6mUelgesY4C4b4apGs1Ee0HmI55V3Fo4p0mwN+K5NfE42Ug1NoftT4v+aSn/k7je2Lv5bikbSo9r49hMXy8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Vehicle = _t, #"KMS at start" = _t, #"KMS at end" = _t, DateTimestart = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Vehicle", type text}, {"KMS at start", Int64.Type}, {"KMS at end", Int64.Type}, {"DateTimestart", type datetime}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"DateTimestart", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Vehicle"}, {{"allrows", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [
a= Table.AddIndexColumn([allrows],"Index",0,1),
b=Table.AddColumn(a,"Exception", each try [KMS at start] - a{[Index]-1}[KMS at end] otherwise 0 )][b]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Vehicle", "KMS at start", "KMS at end", "DateTimestart", "Exception"}, {"Vehicle", "KMS at start", "KMS at end", "DateTimestart", "Exception"})
in
    #"Expanded Custom"

[Daptoid]

Thanks @Vera_33 I really appreciate this. I was thinking of dax solutions but will test this as an M Solution. Thanks again