Forum Discussion

AndrewDang's avatar
AndrewDang
Helper IV
10 years ago
Solved

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 bet...
  • AndrewDang's avatar
    AndrewDang
    10 years ago

    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. 

ankitpatira's avatar
ankitpatira
Community Champion
10 years ago

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's avatar
AndrewDang
Helper IV
10 years ago

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's avatar
    v-caliao-msft
    Microsoft Employee
    10 years ago

    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's avatar
      AndrewDang
      Helper IV
      10 years ago

      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's avatar
        AndrewDang
        Helper IV
        10 years ago

        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