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. 

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.