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 Date...
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"

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"