Forum Discussion
Helper IIIEarliest / latest date calculation based on another column with datetime information / Date Diff
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
Helper IIIGreg_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?
Community Championaggiebrown 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
Working now, many thanks!