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