Color specific cells

[Anonymous]

Hello!

I want to color everything that is before Q1 2024 in green (instead of blue): 

I tried to use the following measure:

CF Gantt =

VAR StartDate =

CALCULATE(

    MIN('reset programs'[Start date]),

    REMOVEFILTERS(dimDate))

VAR EndDate =

CALCULATE(

    MIN('reset programs'[End date]),

    REMOVEFILTERS(dimDate))

VAR ProjectPeriod =

    MIN(dimDate[Date]) >= StartDate

    && MIN(dimDate[Date]) <= EndDate

VAR Status1 =

    MIN('reset programs'[State]) = "Completed"

VAR Country =

    MIN('reset programs'[Country])

VAR Result =

IF (

    Status1,

    2,

    IF (

        ProjectPeriod,

        1

    )

)

RETURN

    Result

So it colors green whenever the 'State' is Completed, but it doesn't color the whole line. So could anyone help me?

[Anonymous · Accepted solution]

Hi @Anonymous 

I understand your problem:

What you need is to set all values before a certain date to green, then set the color of values that have a status of ‘Complete’(The return value is 2) after the date to green, and the others to blue.

I create a set of sample date:

Then create a measure:

color = IF(
            MONTH(MAX('Table'[date]))<3
            &&
            YEAR(MAX('Table'[date]))=2023,
                "green",
//Highlight data prior to March 2023 in green
                IF(
                    MAX('Table'[status])=2,
                        "green",
                        "blue" 
              )
//Data with a status of 2 after March is marked in green, and others are in blue
        )

Create a matrix and right-click the values and click the Conditional formatting > Background color

Field style > (What field should we base this on?)color

The result is as follow:

Best Regards,

Zhengdong Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.