Forum Discussion

JemmaD's avatar
JemmaD
Icon for Helper V rankHelper 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...
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()
    )

govind_021's avatar
govind_021
Icon for Super User rankSuper 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()
)

JemmaD's avatar
JemmaD
Icon for Helper V rankHelper V
1 year ago

Thanks govind_021 this works! 🙂