MAX Date in Facts using DAX

Question

I have an event fact table that is related to Date dimension through date_key and Event Type dimension though event_key (I use SSAS Tabular Model).

I want to get the max date in a fact table with Event Type = "NewEvent" but I need it in date format instead of date_key. I tried this and it's not filtering the correct date.

Last Event Date:= 

VAR EndDateKey =
    IF (
        MAX ( 'Date'[key Date] )
            > MAX ( 'Event'[Key Event Date]),
        MAX ( 'Event'[Key Event Date]),
        MAX ( 'Date'[key Date] )
    )

VAR StartDateKey =
    IF (
        MIN ( 'Date'[key Date] )
            < MIN ( 'Event'[Key Event Date] ),
        MIN ( 'Event'[Key Event Date]),
        MIN ( 'Date'[key Date] )
    )

RETURN CALCULATE(MAX('Date'[Date]), FILTER('Event Type',  'Event Type'[Event Type] = "NewEvent"), FILTER(VALUES('Event'[Key Event Date]), 'Event'[Key Event Date] <= EndDateKey && 'Event'[Key Event Date]>= StartDateKey))

But if I replace it with this, I can get the right max date but it's in date_key format:

RETURN CALCULATE(MAX(**'Event'[Key Event Date]**), FILTER('Event Type',  'Event Type'[Event Type] = "NewEvent"), FILTER(VALUES('Event'[Key Event Date]), 'Event'[Key Event Date] <= EndDateKey && 'Event'[Key Event Date]>= StartDateKey))

How do i that? Sorry newbie with DAX.

Answer 1

You can iterate the Event table with a MAXX function the find the date through the RELATED function. I don’t believe you need all the other start and end date key stuff.

Last Event Date:= 
CALCULATE(
 MAXX(
  'Event',
  RELATED('Date'[Date])
 ),
 'Event Type'[Event Type] = "NewEvent"
)