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

 

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

6 Replies

  • 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