Forum Discussion
aggiebrown
Helper III
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 Ema...
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
Greg_Deckler
Community Champion
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.
aggiebrownHelper III
Helper IIIGreg_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!
- aggiebrown
Greg_Deckler how would I create a measure that would calculate all leads that were emailed within 15 minutes please?