Date Difference HH:MM:SS not showing correctly

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.

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.