topic Re: Consecutive Fail for last 4 months _ in DAX Commands and Tips

Consecutive Fail for last 4 months _

RatanBhushan_05 — Wed, 05 Jul 2023 10:16:30 GMT

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

Re: Consecutive Fail for last 4 months _

v-jianboli-msft — Fri, 07 Jul 2023 06:02:13 GMT

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.

Re: Consecutive Fail for last 4 months _

RatanBhushan_05 — Thu, 13 Jul 2023 12:32:52 GMT

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

Re: Consecutive Fail for last 4 months _

v-jianboli-msft — Fri, 14 Jul 2023 01:35:02 GMT

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.

Re: Consecutive Fail for last 4 months _

RatanBhushan_05 — Fri, 14 Jul 2023 01:38:47 GMT

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.

Re: Consecutive Fail for last 4 months _

v-jianboli-msft — Fri, 14 Jul 2023 05:14:04 GMT

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.

Re: Consecutive Fail for last 4 months _

RatanBhushan_05 — Fri, 14 Jul 2023 07:39:08 GMT

You are a gem of a person.
Love from India

Re: Consecutive Fail for last 4 months _

RatanBhushan_05 — Tue, 25 Jul 2023 08:44:01 GMT

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 -

Re: Consecutive Fail for last 4 months _

RatanBhushan_05 — Tue, 25 Jul 2023 08:45:21 GMT

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

Re: Consecutive Fail for last 4 months _

RatanBhushan_05 — Mon, 20 Nov 2023 10:17:31 GMT

Please help me in this.. Please..