Forum Discussion
Helper IIIEarliest / latest date calculation based on another column with datetime information / Date Diff
hi all,
I am stuck trying to create either a DAX Measure or Virtual DAX table which shows me Latest Date and Time of an Event, and based on that date and time, I want to look for any Calls or Emails that were made since that date. I have built sample data which you can download in the link below. I need to keep the time stamp as I want to know how many minutes / hours it takes to contact a customer. The desired outlook should look something like this:
- ID
- The lastest MQL date in 2023
- Since that datetime, show me the datetime of email sent (only if it was afterwards)
- Since that datetime, show me the datetime of call made (only if it was afterwards)
I tried to build a DAX table to facilitate this with no success, I am unable to build the logic which looks at date intelligence. I can get earliest / latest call & email dates and times for each given lead, but I need it to work with latest MQL date. If contact happened before Latest MQL date it should be showing BLANK(null) value.
Any help would be appreciated. Thank you in advance.
Sample data below:
aggiebrown Try this. PBIX is attached below signature.
Latest MQL Date Measure = MAXX( FILTER( 'fact_table', [total_mql_events] = 1 ), [event_datetime] ) Latest Email Sent = VAR __LatestEvent = [Latest MQL Date Measure] VAR __Table = ADDCOLUMNS( 'fact_table', "__contact_type", RELATED( 'dimmension_table'[contact_type] ) ) VAR __Result = MAXX( FILTER( __Table, [__contact_type] = "EMAIL" && [event_datetime] > __LatestEvent), [event_datetime] ) RETURN __Result Latest Call = VAR __LatestEvent = [Latest MQL Date Measure] VAR __Table = ADDCOLUMNS( 'fact_table', "__contact_type", RELATED( 'dimmension_table'[contact_type] ) ) VAR __Result = MAXX( FILTER( __Table, [__contact_type] = "CALL" && [event_datetime] > __LatestEvent), [event_datetime] ) RETURN __Result
7 Replies
- Greg_Deckler
Community Champion
aggiebrown Try this. PBIX is attached below signature.
Latest MQL Date Measure = MAXX( FILTER( 'fact_table', [total_mql_events] = 1 ), [event_datetime] ) Latest Email Sent = VAR __LatestEvent = [Latest MQL Date Measure] VAR __Table = ADDCOLUMNS( 'fact_table', "__contact_type", RELATED( 'dimmension_table'[contact_type] ) ) VAR __Result = MAXX( FILTER( __Table, [__contact_type] = "EMAIL" && [event_datetime] > __LatestEvent), [event_datetime] ) RETURN __Result Latest Call = VAR __LatestEvent = [Latest MQL Date Measure] VAR __Table = ADDCOLUMNS( 'fact_table', "__contact_type", RELATED( 'dimmension_table'[contact_type] ) ) VAR __Result = MAXX( FILTER( __Table, [__contact_type] = "CALL" && [event_datetime] > __LatestEvent), [event_datetime] ) RETURN __Result
aggiebrownGreg_Deckler hi there, thanks for that. However I need to know the exact time stamps and not just the dates. The next step would be to know how many minutes / hours it takes to contact a customer. Based on Sample Data Lead_1, it took 15 minutes for Email and 30 minutes for a Call. That's why I was more inclined to have a DAX TAble, as I was unaware of being able to see hourly timestamps within DAX Measures?
- Greg_Deckler
aggiebrown You can return date and times with measures. The attached PBIX shows both the date and the time for each email and call. You can use DATEDIFF to get the number of minutes. Updated PBIX is attached below signature with all of that.