Earliest / latest date calculation based on another column with datetime information / Date Diff

[aggiebrown]

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: 

Sample Data 

[Greg_Deckler · Accepted solution]

@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

[aggiebrown]

@Greg_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.

[aggiebrown]

@Greg_Deckler I can see it in your Sample File, however when I am trying to use it in my own I don't get an option to create Date/Time Measure. Do you know why that is? Screeenshot below.

[Greg_Deckler]

@aggiebrown Check your event_datetime column in your data tab, is it a Date/time column?

[aggiebrown]

@Greg_Deckler how would I create a measure that would calculate all leads that were emailed within 15 minutes please? 

[aggiebrown]

Working now, many thanks!