highlighting max & min value in column chart for date/date

[Anonymous]

hi,

kindly ref to the sample file here.

while i'm able to do the conditional formatting to highlight the min/max values for each year in column charts for quarter & month (as shown in the sample file), i'm unable to reflect the same on day/dated-basis.

i managed to get the desired result for the month column chart using following dax formula 

Del-DpAr-Dur-MxMn-M-CF

VAR MaxValue = 
MAXX( ALLSELECTED ( 'Calendar'[Month], 'Calendar'[MonthNo] ), 
[Del-DpAr-Dur] ) 

VAR MinValue = 
MINX( ALLSELECTED ( 'Calendar'[Month], 'Calendar'[MonthNo] ), 
[Del-DpAr-Dur] ) 

VAR Color = 
IF( [Del-DpAr-Dur] = MaxValue, "#F9D987", 
IF( [Del-DpAr-Dur] = MinValue, "#AADBD6", 
"#D3D9B4") ) 
RETURN Color

i'd appreciate any help to hilite the min/max values for the day/dated column chart for each year.

many tks & krgds, 

-nik 

[amitchandak]

@Anonymous , Try like, you can combine them using isinscope

For Date =
VAR MaxValue = MAXX( ALLSELECTED ( 'Calendar'), 'Calendar'[Date] )
VAR MinValue = MINX( ALLSELECTED ( 'Calendar'), 'Calendar'[Date] )

VAR Color =
IF( 'Calendar'[Date] = MaxValue, "#F9D987",
IF( 'Calendar'[Date] = MinValue, "#AADBD6",
"#D3D9B4") )
RETURN Color

For Month
=
VAR MaxValue = eomonth(MAXX( ALLSELECTED ( 'Calendar'), 'Calendar'[Date] ),0)
VAR MinValue = eomonth(MINX( ALLSELECTED ( 'Calendar'), 'Calendar'[Date] ),0)

VAR Color =
IF( eomonth('Calendar'[Date],0)= MaxValue, "#F9D987",
IF( eomonth('Calendar'[Date],0) = MinValue, "#AADBD6",
"#D3D9B4") )
RETURN Color

For Year
=
VAR MaxValue = MAXX( ALLSELECTED ( 'Calendar'), 'Calendar'[Year] )
VAR MinValue = MINX( ALLSELECTED ( 'Calendar'), 'Calendar'[Year] )

VAR Color =
IF( 'Calendar'[Year] = MaxValue, "#F9D987",
IF( 'Calendar'[Year] = MinValue, "#AADBD6",
"#D3D9B4") )
RETURN Color

For Qtr =
VAR MaxValue = MAXX( ALLSELECTED ( 'Calendar'), 'Calendar'[Date] )
VAR MinValue = MINX( ALLSELECTED ( 'Calendar'), 'Calendar'[Date] )

VAR MaxQtr = MAXX( Filter( ALLSELECTED ( 'Calendar'), 'Calendar'[Date] = MaxValue), 'Calendar'[Qtr Year] )
VAR MinQrr = MINX( Filter( ALLSELECTED ( 'Calendar'), 'Calendar'[Date] = MinValue), 'Calendar'[Qtr Year] )

VAR Color =
IF( 'Calendar'[Qtr Year] = MaxQtr, "#F9D987",
IF( 'Calendar'[Qtr Year] = MinValue, "#AADBD6",
"#D3D9B4") )
RETURN Color

[Anonymous]

thanks again, @amitchandak.

i'm sorry if it's a bother. since i'm still not very familiar with the isinscope function especially in using it for conditional formatting, can you kindly run this by again?

to kindly recap, the goal is to be able to hilite the min/max values for the day/dated for each year in the column chart.
tks, -nik

[Anonymous · Accepted solution]

for info, (while i'm still checking) it seems that the following solution that i got help from someone works

VAR _Max_Value =
CALCULATE(
MAXX(
ALLSELECTED( 'Calendar' ) ,
[Del-DpAr-Dur] ) ,
ALLEXCEPT( 'Calendar' , 'Calendar'[Year] ) ,
VALUES( 'Calendar'[Year] ) )

VAR _Min_Value =
CALCULATE(
MINX(
ALLSELECTED ( 'Calendar' ) ,
[Del-DpAr-Dur] ),
ALLEXCEPT( 'Calendar' , 'Calendar'[Year] ) ,
VALUES( 'Calendar'[Year] ) )

VAR _Color =
IF( [Del-DpAr-Dur] = _Max_Value , "#F9D987" ,
IF( [Del-DpAr-Dur] = _Min_Value , "#AADBD6" ,
"#D3D9B4") )

RETURN
_Color

krgds, -nik