Forum Discussion
Create a custom column to calculate time difference based on Rank & Date time
Hi Guys,
I have a unique request to calculate the time difference between 2 orders -
Below is the sample data for your reference
| Order no | Person name | datetime | Rank | Time difference |
| W1088241 | Daniel | 8-26-2019 17:45:00 | 1 | |
| W1088241 | Daniel | 8-26-2019 17:48:00 | 2 | |
| W1088241 | Daniel | 8-26-2019 18:04:00 | 3 | |
| W1088249 | Daniel | 8-26-2019 20:58:00 | 1 | 02:54:00 |
| W1088249 | Daniel | 8-26-2019 21:06:00 | 2 | |
| W1088249 | Daniel | 8-26-2019 21:22:00 | 3 | |
| W1088395 | Daniel | 8-27-2019 16:50:00 | 1 | 19:28:00 |
| W1088395 | Daniel | 8-27-2019 16:57:00 | 2 | |
| W1088395 | Daniel | 8-27-2019 17:10:00 | 3 | |
| W1089241 | Gabriel | 8-27-2019 17:12:00 | 1 | 00:02:00 |
| W1089241 | Gabriel | 8-27-2019 17:31:00 | 2 | |
| W1089241 | Gabriel | 8-27-2019 17:42:00 | 3 | |
| W1089729 | Gabriel | 8-27-2019 18:42:00 | 1 | 01:00:00 |
| W1089729 | Gabriel | 8-27-2019 19:01:00 | 2 | |
| W1089729 | Gabriel | 8-27-2019 19:08:00 | 3 | |
| W1089798 | Gabriel | 8-27-2019 23:28:00 | 1 | 04:20:00 |
| W1089798 | Gabriel | 8-27-2019 23:34:00 | 2 | |
| W1089798 | Gabriel | 8-27-2019 23:51:00 | 3 |
To give you a background on the data; I have order number, person who delivered the order, Date time delivered & Rank
I need to calculate the time difference. I need to calculate the time difference between difference orders i.e. how much time the person takes to deliver from one order to another (that would be his travel time)
For e.g. For Daniel; lets take the Order I have multiple date time entries; he delivered the order number W1088249 (which on which the first date time entry is 8-26-2019 20:58:00 & the previous order number W1088241 for which the last date time entry is 8-26-2019 18:04:00. So the total time for daniel to travel to deliver one order from the other would be (8-26-2019 20:58:00 - 8-26-2019 18:04:00) which is 02:54:00.
Also, I should not consider the difference between two date times when the day changes from 26-27 i.e. first Entry 8-27-2019 16:50:00 for order number W1088395 and last entry 8-26-2019 18:04:00 for order number W1088249 which gives a entire big value 19:28:00 as time difference
Another thing to bear in miind that on the same day there will be many other people delivering orders but we can group it by people. The overall idea is to check how much time people spend in travelling to different locations so that we can then be able to assign orders accordingly.
Any help on this would be appreciated as I am stuck on how to calcuate this. I tried using Min Max rank functions along with datediff but didn't help. So requesting help.
Regards,
Prathamesh Sable
4 Replies
Hi Anonymous ,
You need a new rank column. You could minus it directly and set the data type to time.
Here is my test file for your reference.
Hi v-eachen-msft Thanks for this. However, I need to calculate the difference between orders for resources as well.
i .e. difference between orders should be for individual resource. So my difference in orders should be separate for Daniel and Gabriel
Is this possible?
Regards,
Prathamesh Sable
Hi Anonymous ,
You could edit "Rank2" like the following DAX:
Rank2 = RANKX ( FILTER ( 'Table', 'Table'[Person name] = EARLIER ( 'Table'[Person name] ) ), 'Table'[datetime], , ASC, DENSE )
