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