Forum Discussion

smallfires0628's avatar
smallfires0628
Helper I
2 years ago
Solved

Conditional Format Column Chart to compare Latest Year with Previous Year

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

smallfires0628's avatar
smallfires0628
Helper I
2 years ago

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's avatar
smallfires0628
Helper I
2 years ago

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.