Time in hours between the original date/time completed and the follow-up date/time completed

[BirdyM]

Below is an image trying to illustrate what I am trying to achieve.

https://i.postimg.cc/zDPdgc4t/Repairs.jpg 

I have a 'Repairs' table.  A repair may have an original repair.  I need a DAX measure to calculate the difference in hours between the date/time completed of the original repair (if there was one) and the new repair completed date/time.

I am pretty new to DAX and can't wrap my head around how to do this but I would be really grateful if you could point me in the right direction.

This is some sample data  (-888 indicates that there was no original repair) :-

Repair DWK	Original Repair DWK	Date Completed	Completed Time
-1001006474197500000	-4654889076354150000
2375679257731060000	-6416745726363150000
-5146972346092470000	-5243735374345880000	05/04/2024	1899-12-30 10:33:00.000
1609513929557160000	-888	02/04/2024	1899-12-30 14:51:00.000
-5243735374345880000	-888	02/04/2024	1899-12-30 15:12:00.000
-4317333438224380000	-38075186780666700	04/04/2024	1899-12-30 10:42:00.000
-3347820050601300000	1609513929557160000	04/04/2024	1899-12-30 10:47:00.000
175931987545588000	-1327789092839840000	18/04/2024	1899-12-30 13:03:00.000
-3556921094416860000	-4574284617196100000	05/04/2024	1899-12-30 09:02:00.000
-38075186780666700	-888	02/04/2024	1899-12-30 13:15:00.000
-6416745726363150000	-888	12/04/2024	1899-12-30 16:42:00.000
-1327789092839840000	-888	02/04/2024	1899-12-30 17:33:00.000
-4654889076354150000	-888	12/04/2024	1899-12-30 16:40:00.000
-4574284617196100000	-888	03/04/2024	1899-12-30 11:21:00.000

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZJbjoNADASvssp32NhuP+cqUe5/jTUiKCECsnwxI6gpd8/9fpmM1SsE6lSiQf1crr0tioAhFGqZz22yG+lNSLQXnFUTywT6YRrAIPqdP3tc7xdumjFKyix6sfw+E+SAoMP4nXBgcIawwbJBKDgAKFIatiKmfgvj9Ehy91gm08PJdAttXqQQGTkx6AndH/iMGpu8wgpcGaa2DDuLMiQiq4tJVOp6VB5AuwFsVc28hKlU2XOVmtRCJdU5uJzpS7dUgz4S2MnvrBeMruYd4K0TbSEOB9urWj5C+GcL+9GcWcTHFZ3UTbMR4TD9rwVtEbtJzhY4QPCQ1zV//AE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Repair DWK" = _t, #"Original Repair DWK" = _t, #"Date Completed" = _t, #"Completed Time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{ {"Completed Time", type datetime}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Completed Time", type time}, {"Date Completed", type date}},"en-GB"),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Original Repair DWK"}, #"Changed Type1", {"Repair DWK"}, "Changed Type1", JoinKind.LeftOuter),
    #"Expanded Changed Type1" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type1", {"Date Completed", "Completed Time"}, {"Orig Date Completed", "Orig Completed Time"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Changed Type1", "Hour Difference", each 24 * ( Number.From([Date Completed]&[Completed Time])-Number.From([Orig Date Completed]&[Orig Completed Time])),type number)
in
    #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

[Alex87]

Hello! 

Here is a pattern that you can use to calculate the difference in hours between two date/time columns

Duration Diff (hours) = 
CALCULATE(
    DATEDIFF(
        MIN(Hours[Date Completed]), 
        MIN(Hours[Follow Up Time Completed]),
        HOUR),
    Hours[Original Repair DWK] <> "-888")

If it helps, please mark my reply as the solution

Best regads,

Alex

[BirdyM]

Thanks for your reply.  That will calculate the difference between two dates but the harder part is actually getting the orginal repair completion date which unfornuately that DAX doesn't do