Highest monthly value and date from a measure

[meg222]

Hi all,

I need to create a measure to return the month with the highest awareness. The monthly period column is a date column in the data and "% Aware" is a measure I created.

I've created a measure which returns the highest monthly value which works and returns 56%. The DAX code is:

Max month = MAXX (VALUES( Sheet1[Period - month]), Sheet2[% Aware] )

I also need to get the name of the month with the highest % of awareness so that ultimately I can show something like "Highest month to date: Jul-20 56%" in a card or text box.

Any ideas on how to get the max date as well? I think it will need to be a separate measure to get the date but I just can't work it out.

Thanks,

[FreemanZ]

How are sheet1 and sheet2 related?

[Greg_Deckler · Accepted solution]

@meg222 Try:

Max month name = 
  VAR __Table = SUMMARIZE('Sheet1',[Period - month],"__Aware",[% Aware])
  VAR __MaxAware = [Max Month]
  VAR __Result = MAXX(FILTER(__Table,[__Aware] = __MaxAware),[Period - month])
RETURN
  __Result