How to find datediff in the same column with condition

Question

Hello guys,I have a table that consists of 6 columns. the table looks like this:i want to create the measurement on how long the duration when the 'dtstatus'= 1 until the 'dtstatus' changed to '12'. For example :&nbsp;1:03:37 AM - 12:59:01 AM = 276 seconds&nbsp;2:18:39 AM - 2:12:38 AM = 360 seconds&nbsp;and so on.&nbsp;can anyone helps me on this? thank you in advance:)

sturlaws · Answer

Hi&nbsp;putriwid&nbsp;&nbsp;Can you please:1.&nbsp; Show the sample data in text-tabular format instead of on a pic, so that it can be copied?2. Show the expected result and how you would like to present it (in a visual, or in an additional calculated column in the original table)?Cheers,SturlaIf this post helps, then please consider Accepting it as the solution. Kudos are nice too.

putriwid · Answer

Hi&nbsp;sturlaws&nbsp;, thank you for responding my question.&nbsp;Here's the data sample:&nbsp;dstatus&nbsp;&nbsp;&nbsp; date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Time&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MachineID1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10/29/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 01:01:33&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; B0112&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10/29/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 01:06:11&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; B011&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10/29/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 08:22:27&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; B0112&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10/29/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 08:23:31&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; B011&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10/29/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 13:14:59&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; B0112&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10/29/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 13:37:55&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; B011&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10/29/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14:01:14&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; B0112&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10/29/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14:01:16&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; B011&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10/29/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14:02:21&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; B0112&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10/29/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14:05:34&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; B011&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10/29/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;23:06:38&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; B0112&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10/29/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;23:08:01&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; B011&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10/29/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;23:15:50&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; B01&nbsp;I want to create a measurement on how long the duration (in minutes) when the 'dtstatus' =1 until the 'dtstatus' changed to '12'. &nbsp;i used the formula from the internet:&nbsp;Response Time (Mins) =VAR scTimeStart =CALCULATE ( MIN ('dtlog_bbd'[Time (hh:mm:ss)] ), 'dtlog_bbd'[statusid] = "Tool Problem" )VAR scTimeFinish =CALCULATE ( MAX ( 'dtlog_bbd'[Time (hh:mm:ss)] ), 'dtlog_bbd'[statusid] = "Tools Problem Action" )RETURNIF (HASONEVALUE ( 'dtlog_bbd'[machineid] ),DATEDIFF ( scTimeStart, scTimeFinish, MINUTE ))&nbsp;and i got the result like this:MachineID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Response Time (mins)B01&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10/29/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1327&nbsp;&nbsp;The result that I WANT is(01:06:11 - 01:01:33 ) + (08:23:31 - 08:22:27) + (13:37:55 - 13:14:59) + …. + (23:15:50 - 23:08:01 ) = 44 mins (approx..)&nbsp;While that dax measurement is :(01:06:11 - 01:01:33 ) + (08:22:27 - 01:06:11) +(08:23:31 - 08:22:27) + (13:14:59-08:23:31) +… + (23:15:50 - 23:08:01 ) = 1327 mins.&nbsp;Thank u in advance:)

v-diye-msft · Answer

Hi&nbsp;putriwid&nbsp;
&nbsp;
Let me know if you'd like to get below results:
1. I created a simple sample as your data:

2. Add below M code to add 2 index columns:
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bctBCsAgDATAr5ScBZONrWa/Iv7/GwZ6avE8zJxiUgRqUU0r4jLwDqrJKmn4IdXp/bUvgTbocWxpoI9Ta8TD1o+t5aGmrQ0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [dtstatus = _t, Time = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"dtstatus", Int64.Type}, {"Time", type datetime}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Inserted Division" = Table.AddColumn(#"Added Index", "Division", each [Index] / 2, type number),
    #"Inserted Round Up" = Table.AddColumn(#"Inserted Division", "Round Up", each Number.RoundUp([Division]), Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Round Up",{"Division"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Round Up", "Index2"}})
in
    #"Renamed Columns"

3. Then add below measure:
Measure 2 = var lastindex = MAX('Table (2)'[Index])-1
var lasttime = CALCULATE(MAX('Table (2)'[Time]),FILTER(ALL('Table (2)'),[Index]=lastindex),VALUES('Table (2)'[Index2]))
var timediff = DATEDIFF(lasttime,MAX('Table (2)'[Time]),SECOND)
Return 
timediff

putriwid · Answer

hi sturlaws&nbsp;, thank you for responding my question.&nbsp;&nbsp;Here's the data sample&nbsp;dstatus&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;MachineID1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10/29/2019&nbsp; &nbsp; &nbsp;01:01:33&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B0112&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10/29/2019&nbsp; &nbsp; 01:06:11&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B011&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10/29/2019&nbsp; &nbsp; &nbsp;08:22:27&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B0112&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10/29/2019&nbsp; &nbsp; 08:23:31&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B011&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10/29/2019&nbsp; &nbsp; 13:14:59&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B0112&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10/29/2019&nbsp; &nbsp; &nbsp;13:37:55&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;B011&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10/29/2019&nbsp; &nbsp; &nbsp;14:01:14&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;B0112&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10/29/2019&nbsp; &nbsp; &nbsp;14:01:16&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;B011&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10/29/2019&nbsp; &nbsp; &nbsp;14:02:21&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;B0112&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10/29/2019&nbsp; &nbsp; &nbsp;14:05:34&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;B011&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10/29/2019&nbsp; &nbsp; &nbsp; 23:06:38&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;B0112 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10/29/2019&nbsp; &nbsp; &nbsp; 23:08:01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;B011 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10/29/2019 &nbsp; &nbsp; &nbsp;23:15:50&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;B01I want to create a measurement on how long the duration (in minutes) when the 'dtstatus' =1 until the 'dtstatus' changed to '12'. i used the formula from the internet:Response Time (Mins) =VAR scTimeStart =CALCULATE ( MIN ('dtlog_bbd'[Time (hh:mm:ss)] ), 'dtlog_bbd'[statusid] = "Tool Problem" )VAR scTimeFinish =CALCULATE ( MAX ( 'dtlog_bbd'[Time (hh:mm:ss)] ), 'dtlog_bbd'[statusid] = "Tools Problem Action" )RETURNIF (HASONEVALUE ( 'dtlog_bbd'[machineid] ),DATEDIFF ( scTimeStart, scTimeFinish, MINUTE ))and i got the result like this:Machine ID&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Response Time (mins)B01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10/29/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1327The result that I WANT is(01:06:11 - 01:01:33 ) + (08:23:31 - 08:22:27) + (13:37:55 - 13:14:59) + …. + (23:15:50 - 23:08:01 ) = 44 mins (approx.)&nbsp;While that dax measurement is :(01:06:11 - 01:01:33 ) + (08:22:27 - 01:06:11) +(08:23:31 - 08:22:27) + (13:14:59-08:23:31) +… + (23:15:50 - 23:08:01 ) = 1327 mins.&nbsp;Thank you in advance 🙂

putriwid · Answer

hello&nbsp;v-diye-msft&nbsp;,&nbsp;&nbsp;i have try using the M code that u gave in power query and it looks like this:once i click the 'table' on the custom column, it looks exactly like the one that u share.However, it doesnt show the data from other date.&nbsp;Fyi, M code is very unfamiliar for me. Do you have any simpler measurement?&nbsp;thank you in advance.

Forum Discussion

How to find datediff in the same column with condition

7 Replies

Related content

DATEDIFF DAX

DATEDIFF with condition

conditional datediff

Conditional Column with Multiple Conditions

Conditional column