Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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  noPerson namedatetimeRankTime difference
W1088241Daniel 8-26-2019 17:45:001 
W1088241Daniel 8-26-2019 17:48:002 
W1088241Daniel 8-26-2019 18:04:003 
W1088249Daniel 8-26-2019 20:58:00102:54:00
W1088249Daniel 8-26-2019 21:06:002 
W1088249Daniel 8-26-2019 21:22:003 
W1088395Daniel 8-27-2019 16:50:00119:28:00
W1088395Daniel 8-27-2019 16:57:002 
W1088395Daniel 8-27-2019 17:10:003 
W1089241Gabriel8-27-2019 17:12:00100:02:00
W1089241Gabriel8-27-2019 17:31:002 
W1089241Gabriel8-27-2019 17:42:003 
W1089729Gabriel8-27-2019 18:42:00101:00:00
W1089729Gabriel8-27-2019 19:01:002 
W1089729Gabriel8-27-2019 19:08:003 
W1089798Gabriel8-27-2019 23:28:00104:20:00
W1089798Gabriel8-27-2019 23:34:002 
W1089798Gabriel8-27-2019 23:51:003 

 

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

 

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

1 ACCEPTED SOLUTION

Hi @Anonymous ,

 

Use the "EARLIER" function :

 

Difference3 =
VAR currentindex_ = Sheet2[Index]
VAR t_date =
    CALCULATE (
        MAX ( Sheet2[datetime] ),
        FILTER (
            Sheet2,
            Sheet2[Index] = currentindex_ - 1
                && Sheet2[Rank] = 3
                && Sheet2[Person name]
                    = EARLIER ( Sheet2[Person name] )
        )
    )
RETURN
    DATEDIFF (
        t_date,
        Sheet2[datetime],
        SECOND
    )

 

 Also you can refer to this .pbix

 

Best Regards,

Liang 

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

 

View solution in original post

3 REPLIES 3
V-lianl-msft
Community Support
Community Support

Hi @Anonymous ,

 

First, you can create an index column in "Edit queries" to get the previous "datetime" value.

And then use "DATEDIFF" function to calculate the time difference. 

Difference =
VAR currentindex_ = Sheet2[Index]
RETURN
    DATEDIFF (
        CALCULATE (
            MAX ( [datetime] ),
            FILTER ( Sheet2, Sheet2[Index] = currentindex_ - 1 ),
            FILTER ( Sheet2, Sheet2[Rank] = 3 )
        ),
        [datetime],
        SECOND
    )

Finally, how to convert seconds into standard time can you refer to this blog

Also you can refer to this .pbix

 

Best Regards,

Liang

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

Anonymous
Not applicable

Hi @V-lianl-msft Thanks for this. However, I also 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 ,

 

Use the "EARLIER" function :

 

Difference3 =
VAR currentindex_ = Sheet2[Index]
VAR t_date =
    CALCULATE (
        MAX ( Sheet2[datetime] ),
        FILTER (
            Sheet2,
            Sheet2[Index] = currentindex_ - 1
                && Sheet2[Rank] = 3
                && Sheet2[Person name]
                    = EARLIER ( Sheet2[Person name] )
        )
    )
RETURN
    DATEDIFF (
        t_date,
        Sheet2[datetime],
        SECOND
    )

 

 Also you can refer to this .pbix

 

Best Regards,

Liang 

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

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.