Stacked column chart to show only 3 colors

[athul00000]

hello community 

i need your help to find a solution for the below
i have a stacked column chart like below, x axis is the name and Y axis is the following Dax 

Avg BNFT PerWD 3 months =

VAR MaxDate =

    MAX ( dates[Date] )

VAR MaxDate_3Months =

    DATESINPERIOD ( 'dates 2'[Date], MaxDate, -3, MONTH )

VAR Result =

    CALCULATE (

        DIVIDE ( '#BNFT Sales Measures'[BNFT SALES SWITCH], [NB Working Day] ),

        REMOVEFILTERS ( 'dates' ),

        KEEPFILTERS ( MaxDate_3Months ),

        USERELATIONSHIP ( dates[Date], 'dates 2'[Date] )

    )

RETURN

    Result

i have a month/year filter, so based on this filter selection it will display only 3 months, currently i filtered on Oct/2024 to get the below visual with Aug, Sep, Oct data. Now so far i get the visual correct.

now if i filter Sep/2024, i get the below graph 

now here you can see the color come different based on the months. I need a way to display only 3 colors #FB91DC, #6C91F8, #FF0063. 
so the selected month example - i select Oct/2024 then Oct should have #FF0063, Sep should have 6C91F8, Aug should have #FB91DC. 

same if i select Sep/2024 then Sep should have #FF0063, Aug should have 6C91F8, July should have #FB91DC. 

[athul00000]

hi @rajendraongole1 , thanks for your replay, how can i change the color of the stacked columns ? Could you please help me on this part

[rajendraongole1]

Happy to help @athul00000 - you can do that add conditional formatting by following the steps:

Set Up Conditional Formatting on the Stacked Column Chart:

Select your stacked column chart in Power BI.

In the Visualizations pane, go to the Format section.

Expand the Data colors option.

Apply Conditional Formatting:

Click on the fx (conditional formatting) icon next to Data colors.

In the Conditional formatting window that appears, choose Format by: Field value.

Under Based on field, select the MonthColor measure you created.

Hope this works.

[athul00000]

hi @rajendraongole1 , this is the visual i took 

and i do not have the same options 

[rajendraongole1]

Yes, i am using the same visual . remove if you add anything on the legend. so that you can see fx to do the conditional formatting.

[athul00000]

if i remove the legend i will see like below, and yes now i can see the fx 

but i want to see like this 

[athul00000]

also i forgot to mention , to get stacked by month i used 2 dates table (dates[Date] & dates 2'[Date])  and the DAX below , 

Avg BNFT PerWD 3 months =

VAR MaxDate =

    MAX ( dates[Date] )

VAR MaxDate_3Months =

    DATESINPERIOD ( 'dates 2'[Date], MaxDate, -3, MONTH )

VAR Result =

    CALCULATE (

        DIVIDE ( '#BNFT Sales Measures'[BNFT SALES SWITCH], [NB Working Day] ),

        REMOVEFILTERS ( 'dates' ),

        KEEPFILTERS ( MaxDate_3Months ),

        USERELATIONSHIP ( dates[Date], 'dates 2'[Date] )

    )

RETURN

    Result

[saud968]

Create a Month Index Column:
Add a calculated column to your date table to create an index for the months. This will help in assigning colors based on the selected month.
MonthIndex =
VAR MaxDate = MAX('dates'[Date])
RETURN
DATEDIFF(STARTOFMONTH('dates'[Date]), MaxDate, MONTH)

Modify Your Measure:
Ensure your measure Avg BNFT PerWD 3 months is correctly calculating the values for the last three months based on the selected date.
Apply Conditional Formatting:
Go to the Format pane of your stacked column chart.
Under Data colors, click on the fx button to open the conditional formatting options.
Choose Field value and select the new MonthIndex column.
Set Up the Color Rules:
Define the color rules based on the MonthIndex values:
MonthIndex = 0 (current month): #FF0063
MonthIndex = 1 (previous month): #6C91F8
MonthIndex = 2 (two months ago): #FB91DC
Here’s how you can set up the rules in the conditional formatting dialog:

If MonthIndex is 0, then color is #FF0063.
If MonthIndex is 1, then color is #6C91F8.
If MonthIndex is 2, then color is #FB91DC.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[athul00000]

hi @saud968 , thankyou for your reply, to see the fx i need to remove the legend in the visual if i remove the legend i dont get the visual stacked by month. Do you have another workaround to get like the below 

[rajendraongole1]

Hi @athul00000 - create below measure will determine which color each month should have based on the current selection.

MonthColor =
VAR SelectedMonth = SELECTEDVALUE('dates'[MonthYear]) // Get the selected month/year from the filter
VAR CurrentMonth = FORMAT(MAX(dates[Date]), "MMM yyyy") // Format the current date as "Month Year"

RETURN
SWITCH(
TRUE(),
CurrentMonth = SelectedMonth, "#FF0063", // Selected month color
CurrentMonth = EDATE(SelectedMonth, -1), "#6C91F8", // Previous month color
CurrentMonth = EDATE(SelectedMonth, -2), "#FB91DC", // 2 months before color
"#D3D3D3" // Default color for any other month (in case)
)

This setup uses DAX to dynamically determine the color for each month based on the current selection. As you change the selected month, the color assignments shift accordingly:

The selected month always appears in #FF0063.
The month before it appears in #6C91F8.
Two months before appears in #FB91DC.

Hope this works