How many consecutive days over threshold

[DDDDD]

I need to identify any row when the Value goes over a set threshold for 3 or more consecutive days.

Example data - each ID is repeated once for each Date. If the Value exceeds 4 over a consecutive 3 day period then I need to mark it somehow.

How do I add a column for this?
Here's the type of output I'm looking for - please advise 🙂

[DDDDD]

Thanks both @VahidDM and @CNENFRNL for your quick responses! Kudos to both 🏆🏆

I've used the verison from @CNENFRNL it seems to work a bit better, or simply more understable to my noob brain.
However, it doesn't seem to handle gaps in the date range, but nvm I've adapted so that there's no gaps by adding any missing dates and zero values.
I've also added modified versions of your solution to show the first date on which the threshold was met so that I can answer questions like "when did it start going over threshold" or "for how long has it been over threshold"

Many thanks both :):)

[CNENFRNL · Accepted solution]

Flag = 
VAR __id = DATA[ID]
VAR __pos =
    MAXX(
        FILTER(
            DATA,
            DATA[ID] = __id
                && DATA[Date] <= EARLIER( DATA[Date] )
                && DATA[Value] <= 4
        ),
        DATA[Date]
    )
RETURN
    IF(
        DATA[Date]
            - IF(
                ISBLANK( __pos ),
                MINX( FILTER( DATA, DATA[ID] = __id ), DATA[Date] ) - 1,
                __pos
            ) >= 3,
        "YES"
    )

For fun only, to show off the power of Excel worksheet formula,

[VahidDM]

Hi @DDDDD 

Try this measure:

Warning = 
Var _CD = MAX('Table'[Date])
Var _CD_1 = _CD-1
Var _CD_2 = _CD-2
Var _A = MAX('Table'[Value])
Var _B = CALCULATE(MAX('Table'[Value]),REMOVEFILTERS('Table'[Date]),'Table'[Date]=_CD_1)
Var _C = CALCULATE(MAX('Table'[Value]),REMOVEFILTERS('Table'[Date]),'Table'[Date]=_CD_2)
return
IF(_A>4&&_B>4&&_C>4,"Yes","No")

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

 FIFA Word Cup Medal Records Dashboard:

https://community.powerbi.com/t5/Charticulator-Design-Challenge/FIFA-World-Cup-Medal-Records/cns-p/2...

[CNENFRNL]

Interesting method; but what if 30 consecutive days ...

[VahidDM]

@CNENFRNL 

That measure will manage 30 consecutive days as well:

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

 FIFA Word Cup Medal Records Dashboard:
https://community.powerbi.com/t5/Charticulator-Design-Challenge/FIFA-World-Cup-Medal-Records/cns-p/2...

[CNENFRNL]

I mean, flag the column after 30 consecutive records of a streak; take your own mockup for instance, from 2022/5/30 and onwards.

[VahidDM]

Not sure I got your point correctly or not, but FYI:

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[CNENFRNL]

Hmm... start marking "yes" from 5/30.

[VahidDM]

Yes, because 1-Jun-22 is still in 3 days consecutive period.

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/