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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Measure for time difference between non-consecutive rows?

Hi,

 

I'm fairly new to Power BI so forgive me if this is a common question, I couldn't find anything specific to non-consecutive, variable distance rows. I'm trying to calculate the total delivery time for each truck using the data below as follows: for n deliveries, total route time = Depart(n) - Arrive (1). E.g. for VAN4, total time would be 15:23-10:06 = 5:17.

 

I have no idea how I would go about this. I'm guessing it would be to create a measure that would be filtering by truck first, then find the last in the sequence, and get the time from there; then subtract the earliest in the filtered rows? Even if I was correct in my logic, my knowledge of DAX is very limited at the moment.

 

Truck Name Delivery Sequence Num Arrive Depart
VAN4       1 10:06:00 10:26:00
VAN4       2 11:49:00 12:14:00
VAN4       3 12:45:00 13:00:00
VAN4       4 14:11:00 14:41:00
VAN4       5 15:13:00 15:23:00
VAN3       1 11:11:00 11:36:00
VAN3       2 11:46:00 11:58:00
VAN3       3 17:35:00 17:45:00
VAN2       1 08:26:00 08:38:00
VAN2       2 11:01:00 11:31:00
VAN2       3 12:00:00 12:15:00
VAN2       4 14:44:00 14:59:00
VAN1       1 10:40:00 10:50:00
VAN1       2 13:04:00 13:24:00
VAN1       3 17:31:00 17:43:00
VAN1       4 17:47:00 17:57:00

 

Any help would be greatly appreciated,

Will

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @Anonymous ,

Please refer to my pbix to see if it helps you.

Create a measure.

 

Measure =
VAR _min_arrive =
    CALCULATE (
        MIN ( 'Table'[Arrive] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Truck Name] = SELECTEDVALUE ( 'Table'[Truck Name] )
        )
    )
VAR _max_time =
    CALCULATE (
        MAX ( 'Table'[Depart] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Truck Name] = SELECTEDVALUE ( 'Table'[Truck Name] )
        )
    )
RETURN
    _max_time - _min_arrive

 

vpollymsft_0-1645679596809.png

If I have misunderstood your meaning, please provide your desired output and pbix without privacy information.

 

 

 

Best Regards

Community Support Team _ Polly

 

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Hi @Anonymous ,

Please refer to my pbix to see if it helps you.

Create a measure.

 

Measure =
VAR _min_arrive =
    CALCULATE (
        MIN ( 'Table'[Arrive] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Truck Name] = SELECTEDVALUE ( 'Table'[Truck Name] )
        )
    )
VAR _max_time =
    CALCULATE (
        MAX ( 'Table'[Depart] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Truck Name] = SELECTEDVALUE ( 'Table'[Truck Name] )
        )
    )
RETURN
    _max_time - _min_arrive

 

vpollymsft_0-1645679596809.png

If I have misunderstood your meaning, please provide your desired output and pbix without privacy information.

 

 

 

Best Regards

Community Support Team _ Polly

 

Whitewater100
Solution Sage
Solution Sage

Hello:

 

Please see solution file attached. https://drive.google.com/file/d/1akPQxeppWYb5ePklqODvgHxcSGXejnGI/view?usp=sharing 

 

You might want to play around with the time formats. Hope this helps!

Greg_Deckler
Community Champion
Community Champion

@Anonymous See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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