Forum Discussion

putriwid's avatar
putriwid
Frequent Visitor
6 years ago

How to find datediff in the same column with condition

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...
Need Help
Show and Tell
Tutorial Requests
sturlaws's avatar
sturlaws
Icon for Resident Rockstar rankResident Rockstar
6 years ago

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's avatar
putriwid
Frequent Visitor
6 years ago

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 🙂