cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AdalbertoVal Frequent Visitor
Frequent Visitor

Calculation between dates having as parameter the code of the vehicle

image1.png
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.
2 REPLIES 2
Super User
Super User

Re: Calculation between dates having as parameter the code of the vehicle

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

AdalbertoVal Frequent Visitor
Frequent Visitor

Re: Calculation between dates having as parameter the code of the vehicle

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.
image1.pngimage2.png 

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 296 members 2,798 guests
Please welcome our newest community members: