Calculate difference between two DateTime values in different rows

[saur_dash]

I'm so sorry if a similar question has been answered before, but I'm new to DAX and have been struggling to implement DAX measures I've found on this forum for calculating the difference between DateTime values in different rows.

I am working with a large dataset comprised of invoice numbers and payment statuses, and I need to dynamically calculate the time difference between two filtered statuses as follows:

Source:

Desired Output:

I would ideally like to be able to filter the Start and End Status in the output matrix with a Slicer, any help you could give me to solve this problem would be very much appreciated. Thank you for your time.

[Phil_Seamark · Accepted solution]

Hi @saur_dash

You could try adding this calculated column to your table.

Duration = 

VAR MyLastDate = 
    MINX(
        FILTER(
            'Table1',
            'Table1'[Invoice Number] = EARLIER('Table1'[Invoice Number]) &&
            EARLIER('Table1'[Payment Status]) = "Captured" && 
            'Table1'[Payment Status] = "Authorised"
            ),
        'Table1'[Event Date])
        
RETURN IF(MyLastDate<>BLANK(),FORMAT('Table1'[Event Date]- MyLastDate,"HH:MM:SS") )

It will only show a value on rows with a status of "Captured", and the value will be the value in Hours, Mins and Seconds since the row from the same Invoice Number with a status of "Authorised"

[saur_dash]

Thanks for your help Phil.

I can't wait to try this!

[Phil_Seamark]

This is what I get.  I substituted your lengthy invoice numbers for much simpler version for my testing.

[saur_dash]

That's amazing, thank you Phil! I am working on this now and seeing how you tackled the problem is an eye-opener.

[Phil_Seamark]

HI @saur_dash,

Your post was empty under Source: and Desired Output:, so can you reply and try again? 🙂

[saur_dash]

Thanks for your reply Phil,

Sorry about the error, I've fixed the images. Any help you could give would be greatly appreciated.