How to highlight the max month on a bar graph

[chat_peters]

Hello,

I have a simple bar graph that is sliceable by month. For any selected date range, I would like to highlight the last month in a different color. 

This is the formula I tried passing to the column color part of the power bi report. However, it just colors everything red instead of last month red and the rest in black. Power BI file - link 

MAX MONTH =

Var max_ = CALCULATE(MAX('Calendar'[Short date]),FILTER('Calendar','Calendar'[Short date]=max('Calendar'[Short date])),ALLSELECTED('Calendar'))

Return

SWITCH(TRUE(), max_=MAX('Calendar'[Short date]),"Red","Black")

Jul-12 is the only bar that should be highlighted in red. Can someone please tell me what I am doing wrong with the formula?

[chat_peters]

Thank you so much for replying back. Now when I take the filter off the month slicer this works great.

But if I put a filter selection again, it does something strange. I copy pasted your formula, I didn't change anything in it. I feel like instead of highlighting the sales for the max month, it's highlighting the month where the max sales happened. I want to highlight Aug-14 in the below scenario because that's the Max month in the selection. Thank you again for your patience and thank you for helping!

[Fowmy]

@chat_peters 

Based on your current selection of months Aug has the highest sales and it is highlighted. What is your question please?

[chat_peters]

The max month in my current selection is Aug-14 and it's not highlighted, instead it's highlighting Jul-14. Jul-14 is the month with Max sales. I am not interested in finding the month with the Max sales. I want to highlight the max month for any month selection. 

[Fowmy · Accepted solution]

@chat_peters 

Got it:

MAX MONTH = 
Var __t =  ALLSELECTED( 'Calendar') 
Var __m = MAXX( __t , 'Calendar'[Date] )
Return
IF( __m in VALUES( 'Calendar'[Date] ), "red" , "black")

[chat_peters]

Worked like a dream 🙂

[chat_peters]

@Fowmy Thank you for your response! I put your measure on my power BI report and it works but when I choose a different month range it does what's shown below. I can't figure out why it's not dynamic. 

I also updated my measure to the following. Now before the switch statement part this gives me the correct figure for the units sold for the max month. However, this also doesn't give me the results I want. 😞

Maxmonthrevenue =

Var _max = CALCULATE([Total Sales (Units)],

                            FIlter('Calendar', YEAR('Calendar'[Date]) = MAX('Calendar'[Year])),

FILTER('Calendar',MONTH('Calendar'[Date]) = MAX('Calendar'[Month Number])),ALLSELECTED('Calendar'))

RETURN

SWITCH(TRUE(),

[Total Sales (Units)]=_max,"Red","Black")

realized there was no context in my first measure. 

[Fowmy]

@chat_peters 
Try this measure:

MAX MONTH = 
Var __t = ADDCOLUMNS( ALLSELECTED( 'Calendar') , "Units" , [Total Sales (Units)] ) 
Var __result = MAXX( FILTER( __t , [Units] = MAXX( __t , [Units] ) ) , 'Calendar'[Month(mmm-yy)] )
Return
IF( MAX( 'Calendar'[Month(mmm-yy)] ) = __result , "red" , "black")

[Fowmy]

@chat_peters 

I corrected the measure, please try now:

MAX MONTH = 
Var __t = ADDCOLUMNS(  ALLSELECTED( 'Calendar'[Month(mmm-yy)] , 'Calendar'[Short date]) , "@Units" , [Total Sales (Units)] ) 
Var __m = MAXX( __t , [@Units] )
Var __result = MAXX( FILTER( __t , [@Units] = __m ) , 'Calendar'[Month(mmm-yy)] )
Return
IF( MAX( 'Calendar'[Month(mmm-yy)] ) = __result , "red" , "black")