Check occurance of a value in previous month value

[Antmkjr]

https://drive.google.com/file/d/14SNn2oaQ1m9H9hx1r0QCN0MDnCBei3Fo/view?usp=share_link

In the above report, i need a measure where, IF Month LFL for any previous months is Non LFL , then the following months in the same year should also show Non LFL for that particular category

[tamerj1 · Accepted solution]

Hi @Antmkjr 
Please refer to attached file with the solution

Measure = 
VAR CurrentMonth = MAX ( 'Date'[MonthOfYear] )
VAR CurrentYear = MAX ( 'Date'[Year] )
VAR T1 = CALCULATETABLE ( SUMMARIZE ( Sheet1, 'Date'[Year], 'Date'[MonthOfYear] ), REMOVEFILTERS ( ) ) 
VAR T2 = FILTER ( T1, [MonthOfYear] <= CurrentMonth && [Year] = CurrentYear )
VAR T3 = 
    SELECTCOLUMNS ( 
        T2, 
        "@SPLY", 
        CALCULATE ( [Month LFL], FILTER ( T2, [MonthOfYear] = EARLIER ( [MonthOfYear] ) ), ALL ( 'Date' ) ) 
    )
VAR Result = IF ( "Non LFL" IN T3, "Non LFL", "LFL" ) 
RETURN
    Result