Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Date | Customer | Visiting_Sequence | Start_Time | End_Time |
10/22/2021 | a | 1 | 13:56 | 13:59 |
10/22/2021 | b | 2 | 14:10 | 14:20 |
10/22/2021 | c | 3 | 14:31 | 14:44 |
10/22/2021 | d | 4 | 14:50 | 14:55 |
10/22/2021 | e | 5 | 14:59 | 15:09 |
10/22/2021 | f | 6 | 15:16 | 15:20 |
This is the expected value I would like to get.
Date | Customer | Visiting_Sequence | Start_Time | End_Time | Expected_Value |
10/22/2021 | a | 1 | 13:56 | 13:59 | |
10/22/2021 | b | 2 | 14:10 | 14:20 | 0:11 |
10/22/2021 | c | 3 | 14:31 | 14:44 | 0:11 |
10/22/2021 | d | 4 | 14:50 | 14:55 | 0:06 |
10/22/2021 | e | 5 | 14:59 | 15:09 | 0:04 |
10/22/2021 | f | 6 | 15:16 | 15:20 | 0:07 |
Solved! Go to Solution.
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.
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.
@Anonymous , Is it not
a new column =
datediff([end_time],[Start_time], second)+1
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |