Measure not working, when calculated column worked. Using Average.

[Anonymous]

I have a table with the columns "Value", "Analyte", "Color", "AnalyteType" and "DateEnter". I created a calculated column that averages by all those groups and takes only the last 12 months of data. I would like to move this to a measure though, so I can change which data is used on the fly.

Value	Analyte	Color	AnalyteType	DateEnter
5.44	Balloon	Blue	Lab	2022/05/04
62.5	Car	Red	Lab	2022/03/11
11.5	Car	Purple	Outside	2022/02/08
78.1	Chair	Blue	Indoor	2022/04/22

Here is the calculated column that works:

Column1 = calculate(average(Main[Value]),filter(Main,Main[Analyte]=EARLIER(Main[Analyte])),filter(Main,Main[Color]=EARLIER(Main[Color])),filter(Main,Main[AnalyteType]="Lab"),filter(Main,Main[DateEnter]>today()-365))

Here is the measure I tried:

MeasureDiffs =

average(calculate(average(Main[Final]),filter(Main,Main[Analyte]=EARLIER(Main[Analyte])),filter(Main,Main[Color]=EARLIER(Main[Color])),filter(Main,Main[AnalyteType]="Lab")))

I get the error "The AVERAGE function only accepts a column reference as an argument". I don't understand because it did work as a column in the data table. Any ideas?

[amitchandak · Accepted solution]

@Anonymous , Try like

calculate(average(Main[Final]),filter(allselected(Main),Main[Analyte]=Max(Main[Analyte]) &&  Main[Color]=Max (Main[Color]) && Main[AnalyteType]="Lab"))

[Anonymous]

That worked great. Why use the "Max" instead of "Earlier"?