topic Re: Highest monthly value and date from a measure in DAX Commands and Tips

Highest monthly value and date from a measure

meg222 — Sun, 13 Nov 2022 13:57:05 GMT

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,

Re: Highest monthly value and date from a measure

Greg_Deckler — Sun, 13 Nov 2022 14:13:14 GMT

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

Re: Highest monthly value and date from a measure

FreemanZ — Sun, 13 Nov 2022 14:18:34 GMT

How are sheet1 and sheet2 related?