Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
meg222
Frequent Visitor

Highest monthly value and date from a measure

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.

 

meg222_0-1668347438376.png

 

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,
1 ACCEPTED SOLUTION
Greg_Deckler
Community Champion
Community Champion

@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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
FreemanZ
Super User
Super User

How are sheet1 and sheet2 related?

Greg_Deckler
Community Champion
Community Champion

@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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.