Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Substract start time column from end time column based on date and sequence

Hello everyone,

 

I would like to subtract "start time" from "end time" based on date and sequence. 

we visit customers based on a given sequence within a certain day. I would like to calculate the time spent between customers. 

It will be= Start_Time-End_Time (Based on date column and sequence column)

 

DateCustomerVisiting_SequenceStart_TimeEnd_Time
10/22/2021a113:5613:59
10/22/2021b214:1014:20
10/22/2021c314:3114:44
10/22/2021d414:5014:55
10/22/2021e514:5915:09
10/22/2021f615:1615:20

 

This is the expected value I would like to get.

DateCustomerVisiting_SequenceStart_TimeEnd_Time
Expected_Value
10/22/2021a113:5613:59 
10/22/2021b214:1014:200:11
10/22/2021c314:3114:440:11
10/22/2021d414:5014:550:06
10/22/2021e514:5915:090:04
10/22/2021f615:1615:200:07
Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    Hi, Anonymous 

     

    Try this:

    Measure = 
var _Seq=MAX('Table'[Visiting_Sequence])
var _last_Seq=_Seq-1
var _endOfLast=CALCULATE(MAX('Table'[End_Time]),FILTER(ALL('Table'),'Table'[Visiting_Sequence]=_last_Seq))

var _result=IF(ISBLANK(_endOfLast),BLANK(),
FORMAT(MAX('Table'[Start_Time])-_endOfLast,"hh:mm")
)

return _result

    Result:

    Please refer to the attachment below for details. Hope this helps.

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , Is it not

    a new column =

    datediff([end_time],[Start_time], second)+1

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you for your response
      is it possible to include the sequence column to calculate? there are a lot of customers and a lot of sales reps. and the sequence on the table is kind of mixed up. the only thing that can be used to calculate is the date and the sequence number, for each sales reps.

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Anonymous 

     

    Try this:

    Measure = 
var _Seq=MAX('Table'[Visiting_Sequence])
var _last_Seq=_Seq-1
var _endOfLast=CALCULATE(MAX('Table'[End_Time]),FILTER(ALL('Table'),'Table'[Visiting_Sequence]=_last_Seq))

var _result=IF(ISBLANK(_endOfLast),BLANK(),
FORMAT(MAX('Table'[Start_Time])-_endOfLast,"hh:mm")
)

return _result

    Result:

    Please refer to the attachment below for details. Hope this helps.

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.