Forum Discussion

JemmaD's avatar
JemmaD
Helper V
1 year ago
Solved

Calculate Date Diff where reference matches

Good morning experts,

I have a table of data with a reference, an amount, and the date opened and date closed. 

I want to create the difference between the open and closed date where [Reference] is the same, and the absolute value of [Amount] is the same.

This is because there can be multiple entries for the same reference, so I only know the corresponding dates by the [Amount] column.

 

So I can do DATEDIFF ( [Open Date], Closed Date], DAY ) but I need the other two factors and I don't know how to do that. 

Help!

Need Help
  • govind_021's avatar
    govind_021
    1 year ago

    Hi JemmaD 
    please try below code
    Date Difference =
    VAR CurrentReference = [Reference]
    VAR CurrentAmount = ABS([Amount])
    VAR CurrentOpenDate = [Open Date]
    VAR MatchingRow =
    CALCULATE(
    MIN('YourTableName'[Closed Date]),
    FILTER(
    'YourTableName',
    'YourTableName'[Reference] = CurrentReference &&
    ABS('YourTableName'[Amount]) = CurrentAmount &&
    'YourTableName'[Open Date] = CurrentOpenDate
    )
    )
    RETURN
    IF(
    NOT ISBLANK(MatchingRow),
    DATEDIFF(CurrentOpenDate, MatchingRow, DAY),
    BLANK()
    )

2 Replies

  • govind_021's avatar
    govind_021
    Super User
    1 year ago

    Hi JemmaD 
    please try below code
    Date Difference =
    VAR CurrentReference = [Reference]
    VAR CurrentAmount = ABS([Amount])
    VAR CurrentOpenDate = [Open Date]
    VAR MatchingRow =
    CALCULATE(
    MIN('YourTableName'[Closed Date]),
    FILTER(
    'YourTableName',
    'YourTableName'[Reference] = CurrentReference &&
    ABS('YourTableName'[Amount]) = CurrentAmount &&
    'YourTableName'[Open Date] = CurrentOpenDate
    )
    )
    RETURN
    IF(
    NOT ISBLANK(MatchingRow),
    DATEDIFF(CurrentOpenDate, MatchingRow, DAY),
    BLANK()
    )