Consecutive Fail for last 4 months _

[RatanBhushan_05]

Hi team

I want to highlight every month if prior to 3 months are consecutive failures.

I have attached the PBI File as sample. Please help. 

In Matrix view it should highlite when there are 3 consecvutive failures. Please please please help.

Thanks

[v-jianboli-msft · Accepted solution]

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.

[v-jianboli-msft · Accepted solution]

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.

[RatanBhushan_05]

This is just perfect - Thank you so much - 
Could you please little try to debried what is happening here - please.

[v-jianboli-msft · Accepted solution]

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.

[RatanBhushan_05]

Just to make another scenario - if year changes and we have to count 4 months again from next year Jan - could you please try for that also.? It will be a big favour.

[v-jianboli-msft · Accepted solution]

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.

[RatanBhushan_05]

Hi Jianbo

Could you please look into one more thing which I have been trying - 
If I want to see in the same table the running total - but the catch is if any month is blank then running total will start from one again -

[RatanBhushan_05]

As you can see in the attached pic - i have been trying but insted of starting from 1 it is starting from same previous running total 

[RatanBhushan_05]

Please help me in this.. Please..

[RatanBhushan_05]

You are a gem of a person. 
Love from India

[v-jianboli-msft · Accepted solution]

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.