Calculate Elapsed Time for Different Statuses

[AndrewDang]

Hello all;

I am trying to calculate elapsed time for different statuses for a work order.  For example, I have a work order with different status below. I would like to calculate elapsed time between two statuses, CEST and CPO, for example. It may return 0 or the difference. Please let me know if you have any suggestion on this. 

Thanks in advance for your help. 

[ankitpatira]

@AndrewDang You need to go to query editor and add Index column (starting 0) under Add Column tab to your dataset first. Once done then simply create calculated column using below DAX which will give you difference in seconds,

Difference = ROUND(24. * 60 * 60 * (
YOURTABLE[timeColumn] - IF(
YOURTABLE[IndexColumn] = 0,
YOURTABLE[timeColumn],
(LOOKUPVALUE(
YOURTABLE[timeColumn],
YOURTABLE[IndexColumn],
YOURTABLE[IndexColumn]-1)
))),1)

[AndrewDang]

Thanks @ankitpatira

I have created the index colume as suggested by you. However, the index does not necessary start from 0 since we have too many other work order on the list. 

The image below shows the index for this particular work order. 

I have also created the following Measure but it does not seem to yield the right result. I am playing with this now but would love to have a hint or two from you if possible. 

Difference = ROUND(24. * 60 * 60 * (MIN(factWorkOrderStatuses[StatusDateTime]) - IF(
MIN(factWorkOrderStatuses[Index]) = 0,
MIN(factWorkOrderStatuses[StatusDateTime]),
(LOOKUPVALUE(
factWorkOrderStatuses[StatusDateTime],
factWorkOrderStatuses[Index],
MIN(factWorkOrderStatuses[Index])-1)
))),1)

[v-caliao-msft]

Hi @AndrewDang,

If you would like to calculate the time duration between CEST AND CPO based on the same workorder, then we may take a try with the formula using calculated column below:
elapsedtime1 = DATEDIFF(
                                 LOOKUPVALUE(Sheet1[StatusDateTime], Sheet1[workOrderNumber], value(Sheet1[workOrderNumber]), Sheet1[StatusCode],"CEST"), 
                                 LOOKUPVALUE(Sheet1[StatusDateTime],Sheet1[workOrderNumber],value(Sheet1[workOrderNumber]),Sheet1[StatusCode],"CPO"),
                                 SECOND)

DATEDIFF Function would calculate the duration between two dates, count the result in seconds, minutes or hours, based on the last parameter configured, see:
DATEDIFF Function
And here we use LOOKUPVALUE function to locate the needed two dates.
LOOKUPVALUE Function (DAX)

See the testing result:

If any further assistance needed, please feel free to post back.

Regards,

Charlie Liao

[AndrewDang]

Thanks @v-caliao-msft. I really appreciate your help here.

I have created the calculated column as suggested by you but I run into this error. I will continue to troubleshoot this but any hint from you is also appreciated. The error is: "A tabble of multiple values was supplied where a single value was expected". It looks like it is confused with the value that we tried to pass to the compare. 

Thanks;

Andrew

[AndrewDang]

@v-caliao-msft

Charlie;

I don't know why but I have not been able to get the formula to work yet.  I keep getting the same error "A table of multiple values was supplied where a single value was expected" error. I have been googling but have not been able to identify the root cause of this.

Let me know if you can take another look?

Thanks;

Andrew

[AndrewDang · Accepted solution]

I have found the solution to this problem with help from a friend.  I have created the following: 

StatusStartDate =
CALCULATE(
FIRSTNONBLANK(v_WorkOrderStatuses[StatusDateTime], TRUE()),
ALLEXCEPT(v_WorkOrderStatuses, v_WorkOrderStatuses[WorkOrderNumber], v_WorkOrderStatuses[ChangeGroup],v_WorkOrderStatuses[StatusCode])
)

StatusEndDate =
CALCULATE(
LASTNONBLANK(v_WorkOrderStatuses[StatusDateTime], TRUE()),
ALLEXCEPT(v_WorkOrderStatuses, v_WorkOrderStatuses[WorkOrderNumber], v_WorkOrderStatuses[ChangeGroup], v_WorkOrderStatuses[StatusCode]) )

and the durantion calculation below which returns an integer so that I could use it for my chart:

StatusDuration =
VAR StartDate = CALCULATE(
FIRSTNONBLANK(v_WorkOrderStatuses[StatusStartDate], TRUE),
ALLEXCEPT(v_WorkOrderStatuses, v_WorkOrderStatuses[WorkOrderNumber], v_WorkOrderStatuses[StatusCode], v_WorkOrderStatuses[ChangeGroup])
)

VAR EndDate = CALCULATE(
LASTNONBLANK(v_WorkOrderStatuses[StatusEndDate], TRUE),
ALLEXCEPT(v_WorkOrderStatuses, v_WorkOrderStatuses[WorkOrderNumber], v_WorkOrderStatuses[StatusCode], v_WorkOrderStatuses[ChangeGroup])
)
RETURN
IF( StartDate = EndDate,
(DATEDIFF(StartDate, TODAY(), DAY)),
DATEDIFF(StartDate, EndDate, DAY)

)

Let me know if you have any question on this.