Select Specific Values from a row in a table based on a Date Condition

Hi mmunozjr5 , hello Bibiano_Geraldo  and danextian , thank you for your prompt reply!

Based on Bibiano Geraldo's solution, please create the calculated column as shown below:

ConsecutiveMH = 
VAR CurrentKPI = 'Table'[KPI Name]
VAR CurrentDate = 'Table'[Date]
VAR PreviousMonth1 = EDATE(CurrentDate, -1)
VAR PreviousMonth2 = EDATE(CurrentDate, -2)
VAR NextMonth1 = EDATE(CurrentDate, 1)
VAR ValueCurrent = LOOKUPVALUE('Table'[Value], 'Table'[KPI Name], CurrentKPI, 'Table'[Date], CurrentDate)
VAR ValuePrevious1 = LOOKUPVALUE('Table'[Value], 'Table'[KPI Name], CurrentKPI, 'Table'[Date], PreviousMonth1)
VAR ValuePrevious2 = LOOKUPVALUE('Table'[Value], 'Table'[KPI Name], CurrentKPI, 'Table'[Date], PreviousMonth2)
VAR ValueNext1 = LOOKUPVALUE('Table'[Value], 'Table'[KPI Name], CurrentKPI, 'Table'[Date], NextMonth1)

VAR IsConsecutiveMH = 
    ValueCurrent IN {"M", "H"} &&
    ValuePrevious1 IN {"M", "H"} &&
    ValuePrevious2 IN {"M", "H"}


VAR OnlyMOrBlank = 
    CALCULATE(
        COUNTROWS('Table'), 
        FILTER('Table', 'Table'[KPI Name] = CurrentKPI && ('Table'[Value] = "M" || 'Table'[Value]=BLANK()))
    ) = CALCULATE(
        COUNTROWS('Table'), 
        FILTER('Table', 'Table'[KPI Name] = CurrentKPI)
    )

VAR OnlyHOrBlank = 
    CALCULATE(
        COUNTROWS('Table'), 
        FILTER('Table', 'Table'[KPI Name] = CurrentKPI && ('Table'[Value] = "H" || 'Table'[Value]=BLANK()))
    ) = CALCULATE(
        COUNTROWS('Table'), 
        FILTER('Table', 'Table'[KPI Name] = CurrentKPI)
    )

VAR IsValidAfterSequence = 
    IF(
        OnlyMOrBlank || OnlyHOrBlank, 
        TRUE, 
        NOT(ValueNext1=BLANK() || ValueNext1 = "L")  
    )


RETURN


IF(
    IsConsecutiveMH && IsValidAfterSequence,
    "Yes",
    "No"
)

Result for your reference:

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.