Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello
I'm having a little challenge in a simple table trying to calculate the time passed, for example from 22:00 to 2:00 in the morning.
I have tried this code to calculate the Duration in Minutes it works as long as the times are on the same day, otherwise, it gives me a negative minutes.
I would also like to ask power bi to change the Shift end Date to the next day if it goes past midnight.
I am hoping someone can help me.
Thanks
Solved! Go to Solution.
Thank you so much, Vijay. The formula worked perfectly
Hi Vijay
I didn't have a Start Datetime or End Datetime column to work with. I had to try and create these columns.
This is the only data
It looks like that your shift ends next day. You will need to create Start Datetime and End Datetime like these in PQ
=[Date]&[Start]
=Date.AddDays([Date],1)&[Start]
Then in PQ, you can use following formula for Total Minutes
= Duration.TotalMinutes([EndDateTime]-[StartDateTime])
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTUNzIAIiNDJR0gx8rE1MrAQCHAF8izALJAHEdfpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Start = _t, End = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Start", type time}, {"End", type time}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "StartDateTime", each [Date]&[Start], type datetime),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "EndDateTime", each Date.AddDays([Date],1)&[End]),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "TotalMinutes", each Duration.TotalMinutes([EndDateTime]-[StartDateTime]))
in
#"Added Custom2"
Use below formula for minutes calculation
Duration in Mintes = ([Shift end Date]-[Shift start Date])*1440
Hello Vijay,
Thanks for the formula but
It's still the same problem when the time bleeds over past 12 am into the next day
I would also like to create a new date time that correctly shows the next day for these records. I just merged the date and the end date because I didn't now how to create and end date column and time column any other way.
The shift can't end before the shift starts. This issue will lead to data inconsistency.
Assuming the intention to put this data is correct, then you can use below formula
=ABS([Shift start Date]-[Shift end Date])*1440
Hi Vijay,
This formula works perfectly except for some really do end on the same day
Is there any way to tell the formula that?
Here is what it looks like now using your amazing formula
The only problem if you see some are really starting at 6:30 am and ending on the same day shift at 4:30 pm
Thank again for helping me with this.
Much appreciated.
Here is a better look. For example, some really do start and end on the same shift
for example the start at 7:00:00 AM should still end on the same day 4/1/2021 at 8:00:00pm
also the 12:00 AM to 12:00: AM should all just remain as the same day
No time was really worked on these days
Replacing End Date time with this formula will solve the problem
if [End]>=[Start] then [Date]&[End] else Date.AddDays([Date],1)&[End]
The formula for End DateTime will need to be changed to
if [End]>=[Start] then [Date]&[End] else Date.AddDays([Date],1)&[End]
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTUNzIAIiNDJR0gx8rE1MrAQCHAF8izALJAHEdfpVgdiEojmEoDEytjmCRQnwGUB9QXGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Start = _t, End = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Start", type time}, {"End", type time}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "StartDateTime", each [Date]&[Start], type datetime),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "EndDateTime", each if [End]>[Start] then [Date]&[End] else Date.AddDays([Date],1)&[End]),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "TotalMinutes", each Duration.TotalMinutes([EndDateTime]-[StartDateTime]))
in
#"Added Custom2"
Thank you so much, Vijay. The formula worked perfectly
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
45 | |
19 | |
13 | |
11 |