Forum Discussion
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 contact method (A or B) was the first/earliest.
* If neither, "No Contact"
* If A was first, "Contact A"
* If B was first, "Contact B"
2) Calculate the difference (in days and hours) between the create date and the earliest Contact Method. If no contact, "No Contact".
| ID | Created Date | Contact A Date | Contact B Date |
| ABC123 | 1/4/2024 | 1/11/2024 | 1/7/2024 |
| ABC124 | 1/4/2025 | 1/5/2025 | 1/6/2025 |
| ABC125 | 1/6/2025 | 1/9/2025 | |
| ABC126 | 1/27/2025 | 2/3/2025 | 3/16/2025 |
| ABC127 | 1/31/2025 | ||
| ABC128 | 2/6/2025 | 2/8/2025 | |
| ABC129 | 2/8/2025 | 2/9/2025 | |
| ABC130 | 2/9/2025 | 2/11/2025 | |
| ABC131 | 2/12/2025 | 2/15/2025 | |
| ABC132 | 2/12/2025 | 2/14/2025 | |
| ABC133 | 2/16/2025 | 2/18/2025 | |
| ABC134 | 3/1/2025 | 3/2/2025 | |
| ABC135 | 3/1/2025 | 3/4/2025 | |
| ABC136 | 3/3/2025 | 3/10/2025 | |
| ABC137 | 3/13/2025 | 3/16/2025 | |
| ABC138 | 3/13/2025 | 3/16/2025 | |
| ABC139 | 3/30/2025 | 3/31/2025 | |
| ABC140 | 4/1/2025 | 4/8/2025 | 4/3/2025 |
Any assistance you can provide would be helpful. Thanks!
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"
)
3 Replies
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"
)Please don't use DATE(9999, 12, 31) - it puts unnecessary burden on the data model especially when Auto Date/Time is enabled. Better to leave it BLANK().
Consider using MIN and COALESCE.
Thank you bhanu_gautam, this did the trick!
