Conditional Format Column Chart to compare Latest Year with Previous Year

[smallfires0628]

Hi everyone,

I have a clustered column chart like the one below:

I want to write a dax measure to change the color of the columns for the latest year selected (in above case 2023) to show red if the amount is lower than previous year (2022); else, to show green if the amount is higher. 

I want this comparison to be done on a month by month basis. So for Jan, I want to compare Jan 2023 amount with Jan 2022 amount. For feb, I want to compare Feb 2023 with Feb 2022. 

So basically, for each column for 2023, it should either show red or green. 

[Anonymous · Accepted solution]

Hi @smallfires0628 ,

I did a simple test and you can create something like the following measure applied in conditional formatting. The reference is below:

M_ = 
VAR this_year =
    CALCULATE ( SUM ( 'Table'[Value] ) )
VAR last_year =
    CALCULATE ( SUM ( 'Table'[Value] ), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
RETURN
    IF ( this_year > last_year, "Red", "Green" )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Uzi2019]

Hi @smallfires0628 

Try this video below I hope it helps you.

https://www.youtube.com/watch?v=lpdQmcUZATs&t=231s

Let me know if this worked for you otherwise will provide other solution.

I hope I answered your question!

[smallfires0628]

I tried something like this: 

Conditional Formatting =

var LatestYear = CALCULATE(

    sum('Booking Table'[Total Bookings]),

    FILTER(

        ALLSELECTED('Booking Table'),

        YEAR('Booking Table'[Date]) = MAX('Calendar Table'[Year])

))

var PrevYear = CALCULATE(

    sum('Booking Table'[Total Bookings]),

    FILTER(

        ALLSELECTED('Booking Table'),

        YEAR('Booking Table'[Date]) = MAX('Calendar Table'[Date]) - 1

))

RETURN

IF( LatestYear > PrevYear, "green", "red")

But all my columns turn green. 

[smallfires0628]

My reasoning for this code was something along these lines: 

My column chart has an x-axis with a hierarchy of Month, then year. So I am thinking that my total bookings will be filtered by month first, then year. 

So to calculate the latest year bookings for each month, I would simply have to use the function ALLSELECTED, which from my understanding, keeps the filters from outside. So that means, total bookings in this case will still be filtered by month first, then by year. So to get the total bookings for 2023 for jan, I would simply have to apply max function to YEAR('Calendar Table'date). Similarly for previous year, I'll do the same but just subtract 1 to get total bookings for 2022 for jan.

And with that out of the way, I'll just use the IF function to apply the right conditional formatting.

Is this reasoning flawed? I am quite new to dax, so I still don't understand how PowerBI filters work.