How to Use Aggregate function In PowerBi

Hey,

this measure

Measure = 
var currentYear = MAXX(VALUES('Table1'[Year]),'Table1'[Year])
var itemsMaxYear =
	SELECTCOLUMNS( 
		FILTER(
			ALL('Table1')
			,'Table1'[Year] = currentYear
		)
		,"Item", 'Table1'[Item]
	)
var itemsPrevYear =
	SELECTCOLUMNS( 
		FILTER(
			ALL('Table1')
			,'Table1'[Year] = currentYear - 1
		)
		,"Item", 'Table1'[Item])
var itemsIntersect = 
	INTERSECT(itemsMaxYear, itemsPrevYear)
return
CALCULATE(
	SUM(Table1[Qty])
	,FILTER(
		ALL('Table1')
		,'Table1'[Year] = currentYear &&
		'Table1'[Item] in itemsIntersect
	)
)

creates this output

First the "current" year is stored in a variable. Does the column Year not contribute to the Filter Context (the card visual, and the total row of the table visual, the DAX statement makes sure that the max value is stored to the variable.

This variable is used to create two table variables containing the items of the current year and the previous year (either from the row context or the latest year, using SELECTCOLUMNS(FILTER(...),...)

A 3rd table variable is calculated that finally contains the items present in both years

This 3rd table variable is finally used to filter down the table in combination with the variable currentYear.

Hope this helps

Regards

Tom