Consecutive Fail counts related to calendar period

[Housden996]

Hi 

I am trying to create a column which counts consecutive fails against Periods. 

I am using the following DAX:

Fail Validation = IF(InspectionsMVP[Audit Outcome] = "Fail", 1, 0)

Consecutive Fails = 
VAR FilterPrevPasses =
    FILTER (
        ALL ( InspectionsMVP ),
        InspectionsMVP[Fail Validation] = 0
            && InspectionsMVP[Station] = EARLIER ( InspectionsMVP[Station] )
            && InspectionsMVP[Period Number] <  EARLIER( InspectionsMVP[Period Number]  )
    )
RETURN
    SWITCH (
        TRUE (),
        InspectionsMVP[Fail Validation] = 0, 0,
        CALCULATE ( COUNTROWS ( InspectionsMVP ), FilterPrevPasses ) = 0,
            CALCULATE (
                SUM ( InspectionsMVP[Fail Validation] ),
                FILTER (
                    ALL ( InspectionsMVP ),
                    InspectionsMVP[Station] = EARLIER ( InspectionsMVP[Station] )
                        && 'InspectionsMVP'[Period Number] <= EARLIER( 'InspectionsMVP'[Period Number] )
                )
            ),
        CALCULATE (
            COUNT( InspectionsMVP[Fail Validation] ),
            FILTER (
                ALL ( InspectionsMVP ),
                InspectionsMVP[Station] = EARLIER ( InspectionsMVP[Station] )
                    && InspectionsMVP[Period Number] > CALCULATE ( MAX ( InspectionsMVP[Period Number] ), FilterPrevPasses )
                    && InspectionsMVP[Period Number] <=  EARLIER( InspectionsMVP[Period Number] )
            )
        )
    )

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.

[Fowmy]

@Housden996

Could you explain the how the result should be calculated with an example? Share an Excel file with the expected result and include both pass and fail cases if you have it in that manner.

Yo may share a google drive link for your file here.

[Housden996]

I have granted you access.

[Housden996]

Thanks for the response. 

Here is the link to the .pbix File:
https://drive.google.com/file/d/1BTstgvnSZeWF4MEDgeNqWzpL3e-hsNdw/view?usp=drive_link
The column on the right in this screen shot is what I am trying to achive:

[Fowmy]

@Housden996 

Please check the attached file, I added a supporting column and a measure:

Column

Index = 'Date'[Year]*1000 + 'Date'[Period Number]

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

[Housden996]

Thank you this works for a single instance. 

I just realised I have had you off a bit. 
How would I then scale this to include this for multiple Locations/People/Items ie:

Would I need to create a unique perosn index? So merge person with the indewx you create above? ie CONCATENATE(Index,RELATED(Person))

[Fowmy]

@Housden996 

Please explore the DAX measure that I shared and modify it as per your extended requirments.

[mh2587]

Consecutive Fails = //Try this and replace [Fail Validation] measure with your actual one
VAR CurrentPeriod = InspectionsMVP[Period Number]
VAR PrevPeriod =
    CALCULATE (
        MAX ( InspectionsMVP[Period Number] ),
        FILTER (
            ALL ( InspectionsMVP ),
            InspectionsMVP[Station] = EARLIER ( InspectionsMVP[Station] )
                && InspectionsMVP[Period Number] < CurrentPeriod
        )
    )
RETURN
    IF (
        InspectionsMVP[Fail Validation] = 0,
        0,
        IF (
            ISBLANK ( PrevPeriod ) || InspectionsMVP[Fail Validation] = 1,
            1,
            0
        )
    )

[Housden996]

Hi

Thanks for the reply I have tried this but for every row it is showing up as 1.