Date Diff Matching two different Columns in the Same Table

[raybearz]

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,

[johnt75 · Accepted solution]

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]

it worked. thank you so much!

[raybearz]

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]

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])
)