cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AndrewDang Member
Member

Calculate Elapsed Time for Different Statuses

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. 

 

Elapsed Time Question.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
AndrewDang Member
Member

Re: Calculate Elapsed Time for Different Statuses

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. 

6 REPLIES 6
Super User
Super User

Re: Calculate Elapsed Time for Different Statuses

@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 Member
Member

Re: Calculate Elapsed Time for Different Statuses

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. 

 

 

8-5-2016 10-52-17 AM.jpg

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)

 

 

Moderator v-caliao-msft
Moderator

Re: Calculate Elapsed Time for Different Statuses

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:
Capture.PNGUntitled1.png


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


Regards,

Charlie Liao

Highlighted
AndrewDang Member
Member

Re: Calculate Elapsed Time for Different Statuses

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. 

 

8-15-2016 1-52-28 PM.jpg

 

Thanks;

Andrew

AndrewDang Member
Member

Re: Calculate Elapsed Time for Different Statuses

@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 Member
Member

Re: Calculate Elapsed Time for Different Statuses

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.