Need: Dynamic Column/Measure that conditionally formats VTF% based on VTF% values and Month

[Anonymous]

Hi, I have a table that includes a column for VTF%, and an traffic light icon to the right of that VTF% value. 

The traffic icon color depends on the VTF% and the month of the year.

Below are the parameters: 

Can you help me create a DAX formula for the color code that would dynamically apply the right color (green, yellow, or red) depending on the VTF% and the month of the year?
Thank you!

----------------------------------------------------------------------------

Color Code =

If Fiscal Month = Jul / Oct / Jan / Apr,

              Then if VTF% = -5% to 50%, conditional formatting icon = Green, else if VTF% > 50%, conditional formatting icon = Yellow, else if VTF% < -5%, conditional formatting icon = Red

If Fiscal Month = Aug / Nov / Feb / May,

              Then if VTF% = -5% to 25%, conditional formatting icon = Green, else if VTF% > 25%, conditional formatting icon = Yellow, else if VTF% < -5%, conditional formatting icon = Red

If Fiscal Month = Sep / Dec / Mar / Jun,

              Then if VTF% = -5% to 5%, conditional formatting icon = Green, else if VTF% > 5%, conditional formatting icon = Yellow, else if VTF% < -5%, conditional formatting icon = Red

[Anonymous]

Hi @amitchandak Thank you !!! It worked LIKE A CHARM! You are a genius!!!

[amitchandak · Accepted solution]

@Anonymous , Try a measure like

Color Code =
VAR _v_per = [VTF%] // Assumed it is a measure
VAR _fsmonth = Max(Date[Fiscal Month]) //Assumed it is column and used in vsiaul

RETURN
SWITCH(
TRUE(),
_fsmonth IN {"Jul", "Oct", "Jan", "Apr" } && _v_per >= -.05 && _v_per <= .50, "Green",
_fsmonth IN {"Jul", "Oct", "Jan", "Apr" } && _v_per > .50, "Yellow",
_fsmonth IN {"Jul", "Oct", "Jan", "Apr" } && _v_per < -.05, "Red",

_fsmonth IN {"Aug", "Nov", "Feb", "May" } && _v_per >= -.05 && _v_per <= .25, "Green",
_fsmonth IN {"Aug", "Nov", "Feb", "May" } && _v_per > .25, "Yellow",
_fsmonth IN {"Aug", "Nov", "Feb", "May" } && _v_per < -.05, "Red",

_fsmonth IN {"Sep", "Dec", "Mar", "Jun" } && _v_per >= -.05 && _v_per <= .05, "Green",
_fsmonth IN {"Sep", "Dec", "Mar", "Jun" } && _v_per > .05, "Yellow",
_fsmonth IN {"Sep", "Dec", "Mar", "Jun" } && _v_per < -.05, "Red",

"Other"
)

Also refer

Field Parameters- Conditional Formatting
: https://amitchandak.medium.com/field-parameters-conditional-formatting-517aacc23fdf
Power BI Field Parameters, Keep Axis Sort intact| Always Sort on X/Categorial Axis: https://youtu.be/GfBrB6czByw

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

[Anonymous]

Hi Amitchandak Can you please modify the code to accommodate the following scenarios:

Current code you provided formats color when you select ONE fiscal month, whether it’s Jul, or Nov, or Mar, etc.

But, I would like the code to accommodate the selection of more than 1 fiscal month.

Assume M1 = 1st fiscal month of a fiscal quarter; M2 = 2nd’ M3 = 3rd, etc.

Example:

If user selects M1 + M2 (e.g. Oct + Nov), then the color format code should follow the rules as if only M2 were selected.

If user selects M1 + M2 + M3 (e.g. Oct + Nov + Dec), then the color format code should follow the rules as if only M3 were selected.

Can you modify the code for me? Thanks so much!