Sum of consecutive fails

[Housden996]

Hi 

I am using the following dax to get thefollowing

Measure

Fail Count = 
VAR __CurrIndex = SELECTEDVALUE( 'Date'[Index] )
VAR __SelectedPeriodWithFail = 
   CALCULATE(
        MAXX(
            FILTER(
                ADDCOLUMNS( VALUES( 'Date'[Index]) , "@failcount" , [Sum of Fail Validations] ),
                [@failcount] <> 1 && 'Date'[Index] < __CurrIndex
            ),
            'Date'[Index]
        ),
        ALLSELECTED( 'Date' )
    )
    
VAR __Gap =  FILTER( ALLSELECTED( 'Date'[Index]),'Date'[Index] <= __CurrIndex && 'Date'[Index] > __SelectedPeriodWithFail )

RETURN
   IF( [Sum of Fail Validations] = 1 , COUNTROWS( __Gap ) , BLANK() )

This gives me the output:

This output is incorrect, I need to get the caclulation to output the following:

So if there are multiple tests in a month the consecutive fail is counted as 1 still and if there is no data for the previous month the fail count resets to 0. 

What do i need to change in my calculated column / create a new measure to make this correct? I am totally lost, I have tried to reallign agaisnt the period table but I havnt had much luck.

This used to work for a single instence but now it does.

I need to get this so in the table on the bottom left, attached to this post to show the sites consecutive running strikes.

I got some aid in the past form a user to get the DAX working for a single site but I never managed to get this working for multiple sites.

Can any one help me get this issue boxed off. Its been bugging me for months!

[Housden996]

Link to the pbix file:
https://drive.google.com/file/d/1wTfkA0VwegSVVW_1drECrBW2Czr_ZWdv/view?usp=drive_link

[Greg_Deckler]

@Housden996 Sounds like Cthulhu. Cthulhu - Microsoft Fabric Community

[Housden996]

@Greg_Deckler  thanks for the response, I have attempted to use the cathulhu v2 calculated column DAX and a measure unfortunatly this didnt work. 

I have tried to use my period as the index but the different ways I have aggregated the calculated column the answers are wrong and for some reason the DAX is pulling in a 1 value when there is no fail. 

Examples bellow:

This is for S4

Showing a SUM and MAX of 2 when the Sum/ Max is 4 Fails in a row

So the Cathulhu value for S4 should be 4.

I think this is simlar to the solution you have posted but not the same.

Any suggestions?

[Greg_Deckler]

@Housden996 Post some sample data as text and I'll see what I can do.

[Housden996]

@Greg_Deckler thank you here is a link to the data as I dont have the karma to upload a pbix file:
https://drive.google.com/file/d/1foi4kysvToEN5McUSSzcxSSON_EjBDO0/view?usp=drive_link