Forum Discussion
Overnight stops duration calculation
Hi Guys, I have a list of cars (C,D,E,F,....) arriving for nightstops. Som eof them arrive before midnight, some of them arrive after midnight (which is nigthstop day+1). But all of them depart / return to service nightstop day+1 . I use power BI Query to caluclate few columns with Maximum time of arrival (MaxATA), Minimum time of arrvial (MinATA), Minmim Time of departure( Min ATD) and Max Time of departure( MaxATD). The Maximum ATA is porperly clauclated and put on the same row -- the row with the Nightstop Date, even if the Max ATA is past midnight. But I cannot make the departure tiem (Min ATD) , which is next day, on next row, to be saved on the previous row where the Nightstop Day is caluclated.
See attached excel table. I need to have :
Nightstop Day -- Car -- Real arrival date/time (could be the same day or the next day after midnight) - Real departure (always is next day) and to clauclate the duration how many hours/minutes the car was out of service. (the last three columns are the one I need to calculate).
Could you help with ideas? Either with DAX or in power query .
Thanks 🙂
You have to load the data into power query and next create a custom column that calculates the Nightstop Day for each row and refer this M code:(let
Source = YourSourceData, // Replace with your actual source
#"Added Custom" = Table.AddColumn(Source, "Nightstop Day", each Date.From([Real arrival date/time]))
in
#"Added Custom") This columneffectively giving you the Nightstop Day.
--Next bring the "Real departure" for the next day into the current row for the same car. To do this, you can merge the table with itself based on the Nightstop Day.
Then Duplicate your table in Power Query to create two copies after that Rename the second copy to "Departures." and Create a custom column in the "Arrivals" table to add 1 day to the Nightstop Day.
Refer this M code:(let
Source = YourSourceData, // Replace with your actual source
#"Added Custom" = Table.AddColumn(Source, "Next Day", each [Nightstop Day] + #duration(1, 0, 0, 0))
in
#"Added Custom") Now, merge the "Departures" table with the "Arrivals" table based on the "Car" and "Next Day" columns.
--Create a custom column in the merged table to calculate the duration between "Real arrival date/time" and "Real departure" for each row, and refer this M code:(let
MergedTable = ... // Your merged table here
#"Added Custom" = Table.AddColumn(MergedTable, "Duration", each Duration.From([Real departure] - [Real arrival date/time]))
in
#"Added Custom") and then Load the final merged and transformed data into Power BI.
--create the visuals you need. You can use the "Duration" column you calculated in Power Query to display the duration in hours/minutes, this will allow you to calculate and display the Nightstop Day, arrival time, departure time, and the duration the car was out of service in Power BI.
1 Reply
You have to load the data into power query and next create a custom column that calculates the Nightstop Day for each row and refer this M code:(let
Source = YourSourceData, // Replace with your actual source
#"Added Custom" = Table.AddColumn(Source, "Nightstop Day", each Date.From([Real arrival date/time]))
in
#"Added Custom") This columneffectively giving you the Nightstop Day.
--Next bring the "Real departure" for the next day into the current row for the same car. To do this, you can merge the table with itself based on the Nightstop Day.
Then Duplicate your table in Power Query to create two copies after that Rename the second copy to "Departures." and Create a custom column in the "Arrivals" table to add 1 day to the Nightstop Day.
Refer this M code:(let
Source = YourSourceData, // Replace with your actual source
#"Added Custom" = Table.AddColumn(Source, "Next Day", each [Nightstop Day] + #duration(1, 0, 0, 0))
in
#"Added Custom") Now, merge the "Departures" table with the "Arrivals" table based on the "Car" and "Next Day" columns.
--Create a custom column in the merged table to calculate the duration between "Real arrival date/time" and "Real departure" for each row, and refer this M code:(let
MergedTable = ... // Your merged table here
#"Added Custom" = Table.AddColumn(MergedTable, "Duration", each Duration.From([Real departure] - [Real arrival date/time]))
in
#"Added Custom") and then Load the final merged and transformed data into Power BI.
--create the visuals you need. You can use the "Duration" column you calculated in Power Query to display the duration in hours/minutes, this will allow you to calculate and display the Nightstop Day, arrival time, departure time, and the duration the car was out of service in Power BI.