Date Difference HH:MM:SS not showing correctly

[AvPowerBI]

Hi,

I have pbix file in the below one Drive location

DateDiff 

For some reason the column Sales_DateDiff is not showing correctly for the below highlighted rows

I am expecting them to be the below time differences

Example when running on T-SQL which returns the results I am after

Declare @StartDate as DateTime
Declare @EndDate as DateTime

Set @StartDate = '2020-12-10 15:20:00.000'
Set @EndDate = '2020-12-10 17:20:00.000'


SELECT 
	convert(varchar(2),FORMAT(DATEDIFF(s, @StartDate, @EndDate)/3600,'0#'))+':'+convert(varchar(2),FORMAT(DATEDIFF(s, @StartDate, @EndDate)%3600/60,'0#'))+':'+convert(varchar(2),FORMAT(DATEDIFF(s, @StartDate, @EndDate)%60,'0#')) AS [hh:mm:ss 2]

[PhilipTreacy · Accepted solution]

Hi @AvPowerBI 

Download your PBIX file with modification.

FWIW I'd do all of these time calc in Power Query, but to solve thisnow you can create a column and subtract EndDateTime from StartDateTime

NOTE: in row 5, 6 and 7 the difference between times is exactly as shown in my column.  Your column is out by 1 second.

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[v-jingzhang · Accepted solution]

Hi @AvPowerBI 

I noticed that in your Sales_DateDiff column, you have codes dealing with DateDiff greater than one day, so above solutions are probably not suitable because they forgot to deal with this. You could modify your column codes like below to deal with it.

Sales_DateDiff 2 = 
    VAR _Duration = 'Sales'[EndDateTime] - 'Sales'[StartDateTime]
    VAR _Day = DATEDIFF('Sales'[StartDateTime], 'Sales'[EndDateTime], DAY)
    VAR _Hrs = HOUR(_Duration)
    VAR _Min = MINUTE(_Duration)
    VAR _Sec = SECOND(_Duration)
    VAR _DayStr = IF(_Day = 0, "", _Day & "d ")
    VAR _HrsStr = RIGHT("0" & _Hrs, 2)
    VAR _MinStr = RIGHT("0" & _Min, 2)
    VAR _SecStr = RIGHT("0" & _Sec, 2)
RETURN IF(
    'Sales'[EndDateKey] = -1,
    BLANK(),
    _DayStr & _HrsStr & ":" & _MinStr & ":" & _SecStr
)

 

Additionally, you could also add custom columns in Power Query Editor. Suppose already have StartDateTime and EndDateTime columns in model.

Duration = [End] - [Start]

DateDiff
= (if Duration.Days([Duration])=0 then "" else Number.ToText(Duration.Days([Duration])) &"d ") & Text.End(Duration.ToText([Duration]),8)

 

All codes:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSN9Q3MjAyUDCxMjAAIiUdkJgxRMwUKharg6zSDFklVMwSpjIWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", type datetime}, {"End", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Duration", each [End] - [Start]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "DateDiff", each (if Duration.Days([Duration])=0 then "" else Number.ToText(Duration.Days([Duration])) &"d ") & Text.End(Duration.ToText([Duration]),8))
in
    #"Added Custom1"

Kindly let me know if this helps.

Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

[amitchandak]

@AvPowerBI , Just subtraction in power query or DAx should give you diff in duration .

Try a new column like

new column = [endDatetime] - [startdatetime]

[AllisonKennedy]

@AvPowerBI  You don't need the LOOKUPVALUE to other tables to do this, you can do all of it in Power Query since you're adding columns (Not measures). 

Please try pasting this code into advanced editor of a new blank query and see if you are happy with the results: 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZC5EcAgDAR7ISaQzoChFob+27CQHJjPRDfLLDpRqyMQBecdJDAoS+REciaWlTW/N26sRuE/I6+GhINRunGNrZTFQysmvR1mGLOmG4Pf9zhOLIUkbGPA/iV8Z8D2QJ/RHg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SalesOrder = _t, StartDateKey = _t, StartDateTimeKey = _t, EndDateKey = _t, EndDateTimeKey = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"SalesOrder", Int64.Type}, {"StartDateKey", type text}, {"StartDateTimeKey", type text}, {"EndDateKey", type text}, {"EndDateTimeKey", type text}}),
#"Inserted Date" = Table.AddColumn(#"Changed Type", "Start Date", each Date.From(Text.From([StartDateKey], "en-US")), type date),
#"Inserted Date1" = Table.AddColumn(#"Inserted Date", "End Date", each Date.From(Text.From([EndDateKey], "en-US")), type date),
#"Added Start Time" = Table.AddColumn(#"Inserted Date1", "Start Time", each Time.FromText([StartDateTimeKey])),
#"Added Custom" = Table.AddColumn(#"Added Start Time", "End Time", each Time.FromText([EndDateTimeKey])),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Custom", {{"Start Date", type text}, {"Start Time", type text}}, "en-US"),{"Start Date", "Start Time"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Start Date Time"),
#"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns",{{"Start Date Time", type datetime}}),
#"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type1", {{"End Date", type text}, {"End Time", type text}}, "en-US"),{"End Date", "End Time"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"End Date Time"),
#"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns1",{{"End Date Time", type datetime}}),
#"Inserted Time Subtraction" = Table.AddColumn(#"Changed Type2", "Subtraction", each [End Date Time] - [Start Date Time], type duration),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Time Subtraction",{{"Subtraction", "Date Difference"}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date Difference", type text}})
in
#"Changed Type3"

[PhilipTreacy · Accepted solution]

Hi @AvPowerBI 

Download your PBIX file with modification.

FWIW I'd do all of these time calc in Power Query, but to solve thisnow you can create a column and subtract EndDateTime from StartDateTime

NOTE: in row 5, 6 and 7 the difference between times is exactly as shown in my column.  Your column is out by 1 second.

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.