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. 

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

       

      • AndrewDang's avatar
        AndrewDang
        Helper IV
        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.