Forum Discussion

DDDDD's avatar
DDDDD
Frequent Visitor
4 years ago
Solved

How many consecutive days over threshold

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 🙂

 

 

Need Help
  • CNENFRNL's avatar
    CNENFRNL
    4 years ago
    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,

9 Replies

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    4 years ago

    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/2545975

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    4 years ago
    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,

  • DDDDD's avatar
    DDDDD
    Frequent Visitor
    4 years ago

    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 :):)