Conditional Formatting in Column Chart Based on Year

DAX DAX Calculations
1

I have a column chart that shows number of events that occurred in a specific time frame (selected using date slicer set to “between”). The X axis uses the Dates[Year] from my date table. I want to use conditional formatting for the data color setting in the chart so that the column for the most recent year in the selected date range is a different color. In order to do this, I need a measure that will return a string representing a color value. I can the value returned by that measure to assign the color to each column in the chart.

My problem/question is what is the DAX to test whether the year value for any given date is equal to the year value for the latest date in the date range specified by the slicer. I have already tried the following:

IF (
     SELECTEDVALUE ( Dates[Year] ) = MAX ( Dates[Year] ),
     "#000000",
     "#FFFFFF"
)

That didn’t work because the statement always evaluates to true when used in the column chart. I also tried the following:

IF (
     MAX ( Dates[Year] ) = YEAR ( TODAY() ),
     "#000000",
     "#FFFFFF"
)

This only works if the end date of the selected date range is in the current year. I knew that would be the case, but I tried it anyway just in case. :slight_smile:

Any ideas on how I can achieve the needed results? Unfortunately, I cannot share my PBIX due to confidential data, and I haven’t taken the time to build a sample PBIX with fictitious data yet. I can do that if needed, but I was hoping this description would be enough for someone to point me in the right direction.

Thanks!

2

Hi @DaveC,

With the date table Dates[Year] on the X axis, try something like this

CF Year = 
VAR YearMax = CALCULATE( MAX(Dates[Year]), ALLSELECTED( Dates[Year] ))
RETURN
IF (
     MAX ( Dates[Year] ) = YearMax,
     "#000000",
     "#FFFFFF"
)

I hope this is helpful

3

Thanks! I had to change

ALLSELECTED ( Dates[Date] )

to

ALLSELECTED ( Dates[Year] )

After that, it worked perfectly!

4

Noticed that, I was just updating the post. We pretty much replied at the same time :wink:
Good to know its resolved!