Conditionally colour of bar by year

[Pbiuserr]

Hello,

I've created a measure to color a bar in a chart based on user selection

Colors =

VAR _CurrentYear = YEAR(MAX('Calendar'[Date]))

RETURN

SWITCH(

TRUE(),

_CurrentYear - 3, "#00d600",

_CurrentYear - 2, "#d60000",

_CurrentYear, "#24297A"

)

Somehow I can't let it work. So it would be like - If user click on 2015 then it would be "#24297A" for 2015, "#d60000" for 2014 and "#00d600" for 2013 and so on. If they click 2022 then on then it would be "#24297A" for 2022, "#d60000" for 2021 and "#00d600" for 2020 etc

How to do that?

[v-jingzhang · Accepted solution]

Hi @Pbiuserr 

You need to create a Year table for the slicer and make this table disconnected from Calendar table. 

Then use the following measure

Colors = 
VAR _CurrentYear = SELECTEDVALUE('Year'[Year])
RETURN
SWITCH(
TRUE(),
MAX('Calendar'[Year]) = _CurrentYear - 2, "#00d600",
MAX('Calendar'[Year]) = _CurrentYear - 1, "#d60000",
MAX('Calendar'[Year]) = _CurrentYear, "#24297A"
)

I have created a sample. Please download the attachment to see details. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Arul]

@Pbiuserr ,

This solution in this thread would help you,

https://community.powerbi.com/t5/Desktop/Conditionally-format-bar-chart-column-colour-based-on-slice... 

Thanks,

Arul

[Pbiuserr]

Can you share more details on how to achieve this? I can't figure it out 

[Arul]

@Pbiuserr , You need to create disconnected table and then create a measure for conditional formatting.