Measure to sum Price for each distinct product when columns meet certain conditions

[Anonymous]

Hi Everyone,

Got a little mind twister here 🙂

As per the example below, I would like create a measure to sum the sales for products x and y for the latest year. and check if there was no discount, then sum the sales.

i.e Sum sales of X in year 2003 and Discount(yes) + sum of sales of Y in year 2006 and Discount(yes) = should be equal to 0 for x and 700 for Y = total: 700

Product	Sales	Discount	Year
x	1200	YES	1
x	100	NO	3
y	2300	YES	3
y	500	YES	3
y	3400	YES	2
y	700	YES	6

Thanks in advance

[Greg_Deckler]

Perhaps:

Measure = 
VAR __Table = 
  ADDCOLUMNS(
    SUMMARIZE(
      'Table',
      [Product],
      "__Year",MAX('Table'[Year])
    ),
  "__Sales",SUMX(FILTER('Table','Table'[Product] = [Product] && 'Table'[Year]=[Year] && 'Table'[Discount] = "YES"),'Table'[Sales])
RETURN
SUMX(__Table,[__Sales])
  

Warning, not tested.

[Greg_Deckler · Accepted solution]

OK, minor adjustments, actually tested:

Measure = 
VAR __Table = 
  ADDCOLUMNS(
    SUMMARIZE(
      'Table',
      [Product],
      "__Year",MAX('Table'[Year])
    ),
  "__Sales",SUMX(FILTER('Table','Table'[Product] = EARLIER([Product]) && 'Table'[Year]=EARLIER([__Year]) && 'Table'[Discount] = "YES"),'Table'[Sales])
  )
RETURN
SUMX(__Table,[__Sales])