Colour date when target missed three days in a row

[Belle2024]

Hi, 

I am fairly new to power bi.

I want to be able to format the date so that it is green but turns red when the target is not met three days in a row in a week and then resets again for the next week. 

 

[Anonymous]

Hi, @Belle2024 ,

@lbendlin Thanks for your concern about this case.
Here is my sample data:

You can use this DAX to create a calculated column:

Color = 
VAR CurrentWeek = [Week]
VAR WeekDates = 
FILTER(
    ALL('Table'),
    'Table'[Week] = CurrentWeek
)
VAR RedDays = 
CALCULATE(
    COUNTROWS(
        FILTER(
            WeekDates,
            [Actual] < [Target]
        )
    ),
    DATESINPERIOD(
        'Table'[Date],
        MAX('Table'[Date]), -2, DAY
    )
)
RETURN
IF(
    RedDays >= 3,
    "Red",
    "Green"
)

And the output is as below:

Put the columns into the table visual and follow the steps below:

And the final output is as below:

And if you want a measure not a calculated column, just change the DAX into this:

Color_measure = 
VAR CurrentWeek = MAX([Week])
VAR WeekDates = 
FILTER(
    ALL('Table'),
    'Table'[Week] = CurrentWeek
)
VAR RedDays = 
CALCULATE(
    COUNTROWS(
        FILTER(
            WeekDates,
            [Actual] < [Target]
        )
    ),
    DATESINPERIOD(
        'Table'[Date],
        MAX('Table'[Date]), -2, DAY
    )
)
RETURN
IF(
    RedDays >= 3,
    "Red",
    "Green"
)

The remaining steps are the same.

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

[Belle2024]

1. Hi @Anonymous,

Thanks for your quick response.

I have something like this but I only want it to colour red if the target has been missed for the third day in a row in that week. So say week starting on a Monday if the target was missed Mon, Tue and wed then it would turn red on wed and if target met again in Thursday it would go back to green. Or if targen not met on sat, sun Monday - Monday will still be green as it's the start of a new week if you get me.

[Anonymous]

Hi @Belle2024 ,

Please try this way:
First add a calculated column:

LessThanToday = IF([Actual] < [Target], 1, 0)

Then use this DAX to create a calculated column:

Color = 
VAR CurrentDate = 'Table (2)'[Date]
VAR PreviousDate = CurrentDate - 1
VAR TwoDaysAgo = CurrentDate - 2
VAR LessThanPtev = 
CALCULATE(
    MAX([LessThanToday]),
    FILTER(
        'Table (2)',
        'Table (2)'[Date] = PreviousDate
    )
)
VAR LessThanTwo = 
CALCULATE(
    MAX([LessThanToday]),
    FILTER(
        'Table (2)',
        'Table (2)'[Date] = TwoDaysAgo
    )
)
RETURN
IF(
    [LessThanToday] && LessThanPtev && LessThanTwo,
    "red",
    "green"
)

Set the colors as you did in the steps in the previous reply:

And the final output is as below:

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

[Belle2024]

 

This is great thank you 😊 

• The only thing I would like it to do differently but maybe I'm asking too much is for it to reset at the start of a new week (Monday)so say if the Saturday Sunday Monday are below target Monday will still show as green as it's reset again and Mon Tue wed would have to be below target for it to go red again? So in the attached table the two highlighted would actually be green.

[Anonymous · Accepted solution]

Hi @Belle2024 ,

Oh sorry I forgot about this request.
Please change the DAX into this:

Color = 
VAR CurrentDate = 'Table'[Date]
VAR PreviousDate = CurrentDate - 1
VAR TwoDaysAgo = CurrentDate - 2
VAR CurrentWeek = 'Table'[Week]
VAR LessThanPtev =
CALCULATE(
    MAX([LessThanToday]),
    FILTER(
        'Table',
        'Table'[Date] = PreviousDate && 'Table'[Week] = CurrentWeek
    )
)
VAR LessThanTwo =
CALCULATE(
    MAX([LessThanToday]),
    FILTER(
        'Table',
        'Table'[Date] = TwoDaysAgo && 'Table'[Week] = CurrentWeek
    )
)
RETURN
IF(
    [LessThanToday] && LessThanPtev && LessThanTwo,
    "red",
    "green"
)

To test this, I changed my sample data:

And here is the output:

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

[Belle2024]

This is brilliant thanks so much for your help with it 😊

[lbendlin]

sounds good.  What have you tried and where are you stuck?