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...
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 )
)
johnt75's avatar
johnt75
Icon for Super User rankSuper 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
    Icon for Super User rankSuper 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])
)