DAX Previous Value with multiple categories

[TMO_KY]

Good afternoon Community,

I know this post will be similar to some others so hopefully it doesn't get overlooked as I can't seem to find the answer and I'm getting stumped.  I have a DAX table that I created because I had to use ADDMISSINGITEMS to add in 0's for missing dates.  Now the next step was creating an index on the month-year which I got.  The issue I am having now is returning previous value for specific codes and products.  Here's a table of what I am working with.

index	Month-Year	Codes	Product Family	Failures	Prev Month Value
1	Oct-2022	NSPI	LV	1559	2732
2	Sep-2022	NSPI	LV	2732	3472
3	Aug-2022	NSPI	LV	3472	1786
4	Jul-2022	NSPI	LV	1786	1149
5	Jun-2022	NSPI	LV	1149	2064
6	May-2022	NSPI	LV	2064	3097
1	Oct-2022	NSPI	MS	0	0
2	Sep-2022	NSPI	MS	0	0
3	Aug-2022	NSPI	MS	0	0
4	Jul-2022	NSPI	MS	0	0
5	Jun-2022	NSPI	MS	0	0
6	May-2022	NSPI	MS	0	0
1	Oct-2022	NSPI	PC	34	58
2	Sep-2022	NSPI	PC	58	101
3	Aug-2022	NSPI	PC	101	49
4	Jul-2022	NSPI	PC	49	70
5	Jun-2022	NSPI	PC	70	125
6	May-2022	NSPI	PC	125	69

The Prev Month Value is the expected result column.  I need to pull the previous month failures for each product family AND codes.  I have over 300 codes and 5 products.  I shortened my example to show one code and 3 products.  Any ideas?  I've tried to use: 2 different variations but am not getting the results I need.  Here is one example of dax I'm using 

VAR _PreviousRow = 
CALCULATE (
    MIN ( '24MonthTable'[Index] ),
    ALL ( '24MonthTable' ),
        '24MonthTable'[Failure Code Description]
        = EARLIER ( '24MonthTable'[Failure Code Description] ),
    '24MonthTable'[Month-Year] < EARLIER ( '24MonthTable'[month-year] )
)

RETURN 

CALCULATE( 
    LASTNONBLANKVALUE( '24MonthTable'[Failure Code Description],1 ),
ALL ('24MonthTable' ),
'24MonthTable'[Index] = _PreviousRow
)

Here is the 2nd variation, this one gets me closer but it's still not right:

PrevMonthSum = 
VAR PrevIndx =
CALCULATE(
    MAX( '24MonthTable'[Index] ),
    FILTER(
        '24MonthTable',
        '24MonthTable'[Index] < EARLIER( '24MonthTable'[Index] )
    )
)

VAR  Result =
CALCULATE(
    MAX( '24MonthTable'[Failures] ),
    FILTER(
        '24MonthTable',
        '24MonthTable'[Index] = PrevIndx
    )
)

RETURN
Result

Any help would be greatly appreciated, I'm sure it's a simple fix and I've just gone numb looking at it.

[DataInsights · Accepted solution]

@TMO_KY,

Try this calculated column:

Prev Month Value = 
VAR vIndex = '24MonthTable'[index]
VAR vResult =
    CALCULATE (
        MAX ( '24MonthTable'[Failures] ),
        ALLEXCEPT (
            '24MonthTable',
            '24MonthTable'[Product Family],
            '24MonthTable'[Codes]
        ),
        '24MonthTable'[index] = vIndex + 1
    )
RETURN
    vResult

[TMO_KY]

THANK YOU so much!  I've been racking my brain but now that I see it written and working, it makes sense.  Appreciate it!

[DataInsights]

Glad to hear that!