Automating Column colors for each year

Hi bdehning 

It depends on how the column chart is configured. If a legend is used to split the values by year, automating it isn’t currently possible. Conditional formatting only works when no legend is applied to the column or combo chart. A workaround is to pre-assign values to future years by having them exist in the model even if your current data shows only up to the current and then use a measure that will not return blank for any of the year. Example: 

Some value = 1

Thankyou, amitchandak, cengizhanarslan, RicardoTraNa , danextian, Thejeswar, and Praful_Potphode for your responses.

Hi bdehning,

Based on our understanding, when a field is placed in the Legend of a clustered column chart, Power BI automatically controls the series colours. In this configuration, the “fx” Field Value conditional formatting option is not available. 

As per our research, there is no official documentation confirming that legend-based conditional formatting for column charts is being restored. Therefore, we should consider the current behaviour as the supported design.

As noted by danextian , conditional formatting does not apply when a Legend controls the series. Additionally, as suggested by RicardoTraNa , the correct long term approach is to remove manual colour overrides and allow Power BI to utilise the default theme palette.

Additionally, please refer to the links below:
Apply Conditional Table Formatting in Power BI - Power BI | Microsoft Learn
Customize Power BI Visualization Titles - Power BI | Microsoft Learn
Conditional formatting in Power BI visuals - Power BI | Microsoft Learn
Tips and Tricks for Formatting in Reports - Power BI | Microsoft Learn

We hope the information provided helps to resolve the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

bdehning , If you are not using legend or two measures, you can use field - conditional formatting. You can create a measure like below and use in coditional formatting using field value option 

COlor = Switch(True() ,

max(Table[Inception Year])  = Year(Today()) , "Blue", 

max(Table[Inception Year])  = Year(Today()) -1 , "Green", 

"Red"

) 

How to do conditional formatting by measure and apply it on pie?
https://www.youtube.com/watch?v=RqBb5eBf_I4&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L
https://community.powerbi.com/t5/Community-Blog/Power-BI-Conditional-formatting-the-Pie-Visual/ba-p/1682539
https://amitchandak.medium.com/power-bi-where-is-the-conditional-formatting-option-in-new-format-pane-66e0afcb15f3

Power BI does not support dynamic “auto-assign new color per new category” logic for specific values. The easiest approach would be instead of manually assigning colors, let DAX control the color.

Step 1) Create a color measure

Year Color =
VAR Yr = SELECTEDVALUE('Table'[Inception Year])
RETURN
SWITCH(
    TRUE(),
    Yr = 2024, "#4472C4",
    Yr = 2025, "#ED7D31",
    Yr = 2026, "#70AD47",
    "#A5A5A5"   -- default color for future years
)

Step 2) Apply to visual

• Format → Data colors → fx

• Format by: Field value

• Based on field: Year Color

Hey!  You can use automatic category colors instead of setting a color per year manually.

In your clustered column chart, remove the custom series color overrides and let the visual use the default color palette (Format > Data colors > Reset to default / turn off “Show all”). Then each new Inception Year (2027, 2028, etc.) will automatically get the next color with no yearly maintenance.

If later you need controlled logic, you can switch to conditional formatting with a DAX color measure ... but first step is to stop hardcoding colors per year.

Best Regards! 

Hi bdehning ,

In case our clustered column chart does not use a legend to split the data then you can use a calculated column to apply conditional formatting to the fields. That way every January will have a certain color, February will have a certain color and so on.

It is a limitation that you can use conditional formatting when there is a legend in use in the visual

Regards,

Hi bdehning 

try attached PBIX.please note that i have assumed that we have only X Axis and Y Axis on CLustered column chart.If we have legend field then this apparoch may not work.

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

praful

Hi bdehning,

We would like to follow up and see whether the details we shared have resolved your problem. If you need any more assistance, please feel free to connect with the Microsoft Fabric community.

Thank you.

Hi bdehning,

Thank you for the update. If legend based conditional colour formatting is essential for your scenario, we kindly request you to submit an idea in the Ideas forum using the link: Fabric Ideas - Microsoft Fabric Community so that the product team may evaluate it.

If you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

Hi bdehning,

Thank you for sharing your idea in the Ideas forum. We appreciate your contribution, and we have also upvoted the idea. Please note that Microsoft regularly reviews ideas submitted by the community, and your suggestion may be considered for future updates.

Thank you once again, and we encourage you to continue engaging with the Microsoft Fabric Community.