DAX - Value above threshold 3 months in a row while filtering

[Anonymous]

To illustrate my problem, I'm trying to get a list of the people who have a score above 80 for 3 months in a row.

So from the table below, there would be J in my list but not M.

NAME	DATE	SCORE
J	MAR 2022	90
J	APR 2022	92
J	JUN 2022	98
M	MAR 2022	90
M	APR 2022	45
M	JUN 2022	92

My idea was to create a new colomn which tells me if the person scored above 75 the previons month and the month before that using CALCULATE and DATEADD. However, I don't know how I can first filter only on names?

I tried:

3-month =

VAR name= table[NAME] RETURN

IF(

AND(

AND(table[SCORE]>75,

CALCULATE(AVERAGE(table[SCORE]),table[NAME]=ope, DATEADD(table[DATE].[Date],-1,MONTH))>0.75),

CALCULATE(AVERAGE(table[SCORE]),table[NAME]=name, DATEADD(table[DATE].[Date],-2,MONTH))>0.75),

"3m > 75",

"")

How can I create this column?

Thanks!

[amitchandak · Accepted solution]

@Anonymous , a new column like

new column =
var _1 = eomonth([Date],-1)
var _2 = eomonth([Date],-2)
var _lastm = sumx(filter(Table, [Name] = earlier([Name]) && eomonth([Date],0) =_1),[Score])
var _lastm2 = sumx(filter(Table, [Name] = earlier([Name]) && eomonth([Date],0) =_1),[Score])
return
if(_lastm >75 && _lastm2 >75, 1, 0)

[Anonymous]

Thank you, it worked perfectly!