Forum Discussion

raybearz's avatar
raybearz
Frequent Visitor
3 years ago
Solved

Date Diff Matching two different Columns in the Same Table

Hi all

 

I need to calculate the difference between two dates in the same column by matching a unique identifier from two columns of the same table. Please see below image

 

 

I need to be able to match the transaction ID column with the REFNMBR Column by the same circled ID (197028) and the subtract the dates in the Transaction Date Column. 

 

Does anyone know of a solution?

 

Thanks,

Need Help
Tips and Tricks
  • johnt75's avatar
    johnt75
    3 years ago

    You could add the below as a calculated column

    Date diff =
IF (
    NOT ( ISBLANK ( 'Table'[Ref Number] ) ),
    VAR RefNumber = 'Table'[Ref Number]
    VAR CurrentDate = 'Table'[Transaction date]
    VAR PrevDate =
        LOOKUPVALUE ( 'Table'[Transaction date], 'Table'[Transaction ID], RefNumber )
    RETURN
        DATEDIFF ( PrevDate, CurrentDate, DAY )
)

4 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    You could add the below as a calculated column

    Date diff =
IF (
    NOT ( ISBLANK ( 'Table'[Ref Number] ) ),
    VAR RefNumber = 'Table'[Ref Number]
    VAR CurrentDate = 'Table'[Transaction date]
    VAR PrevDate =
        LOOKUPVALUE ( 'Table'[Transaction date], 'Table'[Transaction ID], RefNumber )
    RETURN
        DATEDIFF ( PrevDate, CurrentDate, DAY )
)
    • raybearz's avatar
      raybearz
      Frequent Visitor
      3 years ago

      Hi John

       

      Seems like this will work, however, there can be multiple rows of the same Reference ID for each transaction ID

       

      I am getting this message returned:

       

       

      Any way around this?

      • johnt75's avatar
        johnt75
        Super User
        3 years ago

        If there are multiple entries with the same transaction ID you'll need to decide which one is the correct one to use - earliest, most recent, some other criteria etc. Then you can you replace the LOOKUPVALUE with some variation on

        SELECTCOLUMNS( CALCULATETABLE( TOPN( 1, 'Table', sort criteria), 
REMOVEFILTERS('Table'),
'Table'[Transaction ID] = RefNumber),
"@val", 'Table'[Transaction date])
)