Forum Discussion

saur_dash's avatar
saur_dash
New Member
8 years ago
Solved

Calculate difference between two DateTime values in different rows

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.

Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    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"

6 Replies

    • saur_dash's avatar
      saur_dash
      New Member
      8 years ago

      Thanks for your reply Phil,

       

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

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    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's avatar
      saur_dash
      New Member
      8 years ago

      Thanks for your help Phil.

       

      I can't wait to try this!

      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        8 years ago

         

         

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