Consecutive Fail for last 4 months _

Hi RatanBhushan_05 ,

Please try:

Measure = 
var _a = COUNTX(FILTER(ALL('Table'),[KPI]=SELECTEDVALUE('Table'[KPI])&&[Date]>EOMONTH(SELECTEDVALUE('Table'[Date]),-1)&&[Date]<=EOMONTH(SELECTEDVALUE('Table'[Date]),3)&&[Value]="Fail"),[Value])
var _b = COUNTX(FILTER(ALL('Table'),[KPI]=SELECTEDVALUE('Table'[KPI])&&[Date]>EOMONTH(SELECTEDVALUE('Table'[Date]),-2)&&[Date]<=EOMONTH(SELECTEDVALUE('Table'[Date]),2)&&[Value]="Fail"),[Value])
var _c = COUNTX(FILTER(ALL('Table'),[KPI]=SELECTEDVALUE('Table'[KPI])&&[Date]>EOMONTH(SELECTEDVALUE('Table'[Date]),-3)&&[Date]<=EOMONTH(SELECTEDVALUE('Table'[Date]),1)&&[Value]="Fail"),[Value])
var _d = COUNTX(FILTER(ALL('Table'),[KPI]=SELECTEDVALUE('Table'[KPI])&&[Date]>EOMONTH(SELECTEDVALUE('Table'[Date]),-4)&&[Date]<=EOMONTH(SELECTEDVALUE('Table'[Date]),0)&&[Value]="Fail"),[Value])
return IF(_a=4||_b=4||_c=4||_d=4,"Green","White")

Final output:

Best Regards,

Jianbo Li

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

Hi RatanBhushan_05 ，

Its calculation logic is this:

imagine four consecutive months as four fixed positions, consider the current month in the four positions respectively, that is, the variables a, b,c,d as long as one of these four variables meets the requirements, then it means that it is in the four consecutive months.

Best Regards,

Jianbo Li

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

Hi RatanBhushan_05 ,

Please try:

Measure =
VAR _a =
    COUNTX (
        FILTER (
            ALL ( 'Table' ),
            [KPI] = SELECTEDVALUE ( 'Table'[KPI] )
                && [Date] > EOMONTH ( SELECTEDVALUE ( 'Table'[Date] ), -1 )
                && [Date] <= EOMONTH ( SELECTEDVALUE ( 'Table'[Date] ), 3 )
                && YEAR ( [Date] ) = YEAR ( SELECTEDVALUE ( 'Table'[Date] ) )
                && [Value] = "Fail"
        ),
        [Value]
    )
VAR _b =
    COUNTX (
        FILTER (
            ALL ( 'Table' ),
            [KPI] = SELECTEDVALUE ( 'Table'[KPI] )
                && [Date] > EOMONTH ( SELECTEDVALUE ( 'Table'[Date] ), -2 )
                && [Date] <= EOMONTH ( SELECTEDVALUE ( 'Table'[Date] ), 2 )
                && YEAR ( [Date] ) = YEAR ( SELECTEDVALUE ( 'Table'[Date] ) )
                && [Value] = "Fail"
        ),
        [Value]
    )
VAR _c =
    COUNTX (
        FILTER (
            ALL ( 'Table' ),
            [KPI] = SELECTEDVALUE ( 'Table'[KPI] )
                && [Date] > EOMONTH ( SELECTEDVALUE ( 'Table'[Date] ), -3 )
                && [Date] <= EOMONTH ( SELECTEDVALUE ( 'Table'[Date] ), 1 )
                && YEAR ( [Date] ) = YEAR ( SELECTEDVALUE ( 'Table'[Date] ) )
                && [Value] = "Fail"
        ),
        [Value]
    )
VAR _d =
    COUNTX (
        FILTER (
            ALL ( 'Table' ),
            [KPI] = SELECTEDVALUE ( 'Table'[KPI] )
                && [Date] > EOMONTH ( SELECTEDVALUE ( 'Table'[Date] ), -4 )
                && [Date] <= EOMONTH ( SELECTEDVALUE ( 'Table'[Date] ), 0 )
                && YEAR ( [Date] ) = YEAR ( SELECTEDVALUE ( 'Table'[Date] ) )
                && [Value] = "Fail"
        ),
        [Value]
    )
RETURN
    IF ( _a = 4 || _b = 4 || _c = 4 || _d = 4, "Green", "White" )

Best Regards,

Jianbo Li

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