How to find datediff in the same column with condition

[putriwid]

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 :

 1:03:37 AM - 12:59:01 AM = 276 seconds

 2:18:39 AM - 2:12:38 AM = 360 seconds 

and so on.

can anyone helps me on this? thank you in advance:)

[v-diye-msft]

Hi @putriwid 

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]

hello @v-diye-msft ,

•  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.

• Fyi, M code is very unfamiliar for me. Do you have any simpler measurement? 

thank you in advance.

[sturlaws]

@putriwid ,

you can use this to created a calculated column of durations:

StartTime =
VAR currentLineDateTime =
    CALCULATE ( MIN ( 'Table'[date] ) )
VAR currentLineStatus =
    CALCULATE ( MIN ( 'Table'[dstatus] ) )
RETURN
    IF (
        currentLineStatus = 12;
        DATEDIFF (
            CALCULATE (
                MAX ( 'Table'[date] );
                FILTER (
                    ALL ( 'Table' );
                    'Table'[date] < currentLineDateTime
                        && 'Table'[dstatus] = 1
                )
            );
            currentLineDateTime;
            SECOND
        );
        BLANK ()
    )

[v-diye-msft]

Hi @putriwid 

let me specify more detailed steps of the power query:

1. Add the index column from 1 to add the first index column:

2. Inserted the division using divide by 2

3. Round up the values

4. Delete the Division column and change the "Round up" column as "Index 2"

5. Then try the measure I provided before

If you can't fix it, Please kindly share your dummy pbix here (Upload it to dropbox/onedrive and generate the link), that I can draw it up for you. 

[sturlaws]

Hi @putriwid 

Can you please:

1.  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,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[putriwid]

Hi @sturlaws , thank you for responding my question.

Here's the data sample:

dstatus    date              Time         MachineID

1        10/29/2019       01:01:33         B01

12       10/29/2019      01:06:11          B01

1        10/29/2019       08:22:27         B01

12       10/29/2019       08:23:31         B01

1       10/29/2019       13:14:59         B01

12      10/29/2019       13:37:55        B01

1        10/29/2019       14:01:14         B01

12        10/29/2019       14:01:16         B01

1       10/29/2019       14:02:21         B01

12       10/29/2019       14:05:34         B01

1       10/29/2019       23:06:38         B01

12       10/29/2019       23:08:01         B01

1       10/29/2019       23:15:50         B01

I 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" )

RETURN

IF (

HASONEVALUE ( 'dtlog_bbd'[machineid] ),

DATEDIFF ( scTimeStart, scTimeFinish, MINUTE ))

and i got the result like this:

MachineID                          Date                      Response Time (mins)

B01                         10/29/2019                         1327

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..)

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.

Thank u in advance:)

[putriwid]

hi @sturlaws , thank you for responding my question.

 Here's the data sample 

dstatus         date               Time         MachineID
1               10/29/2019     01:01:33          B01
12              10/29/2019    01:06:11          B01
1               10/29/2019     08:22:27          B01
12              10/29/2019    08:23:31          B01
1                10/29/2019    13:14:59          B01
12              10/29/2019     13:37:55         B01
1                10/29/2019     14:01:14         B01
12              10/29/2019     14:01:16         B01
1                10/29/2019     14:02:21         B01
12              10/29/2019     14:05:34         B01
1               10/29/2019      23:06:38         B01
12             10/29/2019      23:08:01         B01
1               10/29/2019      23:15:50         B01

I 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" )
RETURN
IF (
HASONEVALUE ( 'dtlog_bbd'[machineid] ),
DATEDIFF ( scTimeStart, scTimeFinish, MINUTE ))

and i got the result like this:
Machine ID          Date                Response Time (mins)
B01                    10/29/2019             1327

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.)

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.

Thank you in advance 🙂