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.