Forum Discussion

MuppetyMe's avatar
MuppetyMe
Icon for Helper I rankHelper I
1 year ago
Solved

Assistance identifying earliest date, method, and finding difference between 2 dates

I have a dataset with 4 columns in a single table; a unique identifier (ID), create date, and 2 date columns with different contact methods. Here's what I want to accomplish:  1) Identify which co...
Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    MuppetyMe  Create a new column that identifies the earliest contact method:

    Earliest Contact =
    VAR ContactADate = IF(ISBLANK('Table'[Contact A Date]), DATE(9999, 12, 31), 'Table'[Contact A Date])
    VAR ContactBDate = IF(ISBLANK('Table'[Contact B Date]), DATE(9999, 12, 31), 'Table'[Contact B Date])
    RETURN
    IF(
    ISBLANK('Table'[Contact A Date]) && ISBLANK('Table'[Contact B Date]),
    "No Contact",
    IF(
    ContactADate < ContactBDate,
    "Contact A",
    "Contact B"
    )
    )

     

    Then create a new column that calculates the difference in days and hours:

    DAX
    Contact Difference =
    VAR EarliestContactDate =
    SWITCH(
    'Table'[Earliest Contact],
    "Contact A", 'Table'[Contact A Date],
    "Contact B", 'Table'[Contact B Date],
    BLANK()
    )
    RETURN
    IF(
    ISBLANK(EarliestContactDate),
    "No Contact",
    VAR DiffDays = DATEDIFF('Table'[Created Date], EarliestContactDate, DAY)
    VAR DiffHours = DATEDIFF('Table'[Created Date], EarliestContactDate, HOUR) - (DiffDays * 24)
    RETURN DiffDays & " days " & DiffHours & " hours"
    )