Calculation between dates having as parameter the code of the vehicle

[Anonymous]

Meus amigos, tenho um banco de dados no excel onde tenho 3 colunas. A com código de veículo. B com data de chegada em uma cidade. C com diferença em dias, calculada com a última chegada me uma cidade e o penultima. este cálculo se repete várias vezes por vários dias com base no id do veículo, como faço no power bi, seja dax ou M.

[Anonymous]

Duration type things are good to do in PoweQuery in my opinion. I have attached the file below, but here's the general steps:

• Group rows by Vehicle, aggregate by all rows
• sort the date of each sub-table from low to high
• Add and index column from 1 and another from 0 in each sub-table
• Join each sub-table with itself
• Expand the date ( which is now called prev date)
• Add a column that subtracts the current rows date from the prev row date
• Remove misc columns
• Expand the remaining and set data types

PQ code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkvNyEzOSVXSUXJJLElVitWBCykYAgUN9Q31jQwMLVEkjCASBpgyxmAZYyx6QIYZ6RvjNMwIu2EgYewyJvowLbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Vehicle", type text}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Vehicle"}, {{"Data", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Sort( [Data],{"Date",Order.Ascending})),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.AddIndexColumn(
[Custom],"Index1", 1,1)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each Table.AddIndexColumn(
[Custom.1],
"Index0",
0,1)),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each Table.NestedJoin(
[Custom.2],{"Index0"},
[Custom.2],{"Index1"},
"Join",
JoinKind.LeftOuter
)),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Custom.4", each Table.ExpandTableColumn([Custom.3], "Join", {"Date"}, {"Prev Date"})),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Custom.5", each Table.AddColumn(
[Custom.4],
"Days",
each
Duration.Days( [Date] - [Prev Date]))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom5",{"Custom.5"}),
    #"Expanded Custom.5" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom.5", {"Vehicle", "Date", "Days"}, {"Vehicle", "Date", "Days"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Custom.5",{{"Vehicle", type text}, {"Date", type date}, {"Days", Int64.Type}})
in
    #"Changed Type2"

File:

https://1drv.ms/u/s!Amqd8ArUSwDS0Rb9c9ZDZcqZyB4S

[Anonymous]

Nick, good morning. Sorry for the time. First of all I would like to thank you very much for your help and your willingness, thank you very much.

Another doubt, I saw that in the dax that you developed, it generates a kind of consolation.

Is it possible for days between one date and another to be displayed on a daily basis? 
Why do I need the information open per vehicle based on the day? I am sending two images, one shows the data open, 
the other shows the purpose (purpose) of the numbers.
as it is in the image, thank you again and ask for the disturbance.