calculate Date & Time differance

[MEHUL123]

Hi,

Pls help to provide me DAX for following :

Colunm1(DATE/TIME1) : 31-10-2024   23:55:00

Colunm2(DATE/TIME2) : 01-11-2024   17:06:00

Colunm3 : Needed Time differance in minutes, i.e. 17hrs (1020 minutes) ,11mins = 1020+11=1031 minutes

Thanks in advance

[PhilipTreacy · Accepted solution]

Hi @MEHUL123 

Download example PBIX file

Why does it have to be done in DAX?  It's much easier in Power Query.  DAX doesn't have the functions to do this type of calculation easily.

In Power Query just subtract the first column from the 2nd.  You can then convert the result to a Duration data type and then calculate minutes from the hours and mins (and days if you have any)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjbUNTTQNTIwMlEwMrYyNbUyMFDSUTIAihpCRA3NrQzMQKKxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type datetime}, {"Column2", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Column2]-[Column1]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type duration}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Minutes", each Duration.Hours([Custom]) * 60 + Duration.Minutes([Custom])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"})
in
    #"Removed Columns"

Regards

Phil

[v-xinruzhu-msft · Accepted solution]

Hi,

Thanks for the solution PhilipTreacy offered, and i want to offer some more information for user to refer to.

hello @MEHUL123 , you can create a calculated column like the following.

Column = DATEDIFF([Column1],[Column2],MINUTE)

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PhilipTreacy · Accepted solution]

Hi @MEHUL123 

Download example PBIX file

Why does it have to be done in DAX?  It's much easier in Power Query.  DAX doesn't have the functions to do this type of calculation easily.

In Power Query just subtract the first column from the 2nd.  You can then convert the result to a Duration data type and then calculate minutes from the hours and mins (and days if you have any)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjbUNTTQNTIwMlEwMrYyNbUyMFDSUTIAihpCRA3NrQzMQKKxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type datetime}, {"Column2", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Column2]-[Column1]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type duration}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Minutes", each Duration.Hours([Custom]) * 60 + Duration.Minutes([Custom])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"})
in
    #"Removed Columns"

Regards

Phil