Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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