Checking for 3 consecutive temps of 20 and under

[PolAmer]

I have a table with dates and Avg temps which update multiple times a day. I'm trying to highlight the Avg. Temp cells only when 3 consecutive dates have temparatures of 20 degrees or lower. Is there a way in PowerBI to acomplish this? 
 
Original Data

Date	Avg Temp
2/10/2025	20
2/11/2025	22
2/12/2025	19
2/13/2025	29
2/14/2025	21
2/15/2025	20
2/16/2025	18
2/17/2025	7

Final Result

Date	Avg Temp
2/10/2025	20
2/11/2025	22
2/12/2025	19
2/13/2025	29
2/14/2025	21
2/15/2025	20
2/16/2025	18
2/17/2025	7

[GiuseppeMR]

Hi,

Do you expect the following result?

If so, try this approch:

• define the measure Check:

check = 
if(
    [sum val] = 20 && 
    calculate(
        [sum val], 
        DATEADD(Tabella[date], 1, DAY) )<20 &&
    calculate(
        [sum val], 
        DATEADD(Tabella[date], 2, DAY) )<20 ,
    "ok",
    "ko"
    )

• define a metric color:

color = 
if(
    [check] = "ok", 1,
    if(
        CALCULATE([check], DATEADD(Tabella[date], -1, DAY)) = "ok" ||
        CALCULATE([check], DATEADD(Tabella[date], -2, DAY)) = "ok",
        1, 0))

• define the conditional formatting on your measure:

Note: this approch should work if all dates are consecutive

[PolAmer]

Hi GiuseppeMR,
I've updated my origianl message. Your measure check did not work for me.

[ToddChitt]

Ah, so if I understand you correctly now, it has to be three days in a row below 20, not just any ONE day below 20, correct?

If so, you should investigate Visual Calculation, and create a DAX calc at the Visual Level that is the MAX of the last three days. But I'm not sure how you would get the formatting to be right for the 15th as the MAX for the last three days on the 15 would be 29.

Let me ask this: What is the problem you are trying to solve? It may not be exact, but a rolling average may be close. FYI, that is also available in Visual Calculations DAX.

[PolAmer]

Hi ToddChitt,
I'm don't see an function in Conditional Fromatting that would check for 3 consecutive 20 and lower integers and then highlight them. 

[ToddChitt]

In the Fields section, click the optons next to the field you want to format (in this case, [Ave Temp]) and select "Conditional Formatting" and follow the prompts.