Calculating Variance based of Row Criteria

[kennym9]

Hey Guys,

I'm trying to calculate the variance of units sold of a product based on period code, model, color, etc... Normaly I use the quick measure tool to handle this task but the way my table is setup, it wont allow me to.

This is what my table looks like, P2 being the latest time period, P1 being the previous time period.

Any help with this dax function would be greatly appreciated as I'm still getting familiar with dax calculations. Thank You!

[Greg_Deckler · Accepted solution]

@kennym9 So like this?

Measure = 
  VAR __Period = MAX('Table'[Period Code])
  VAR __Model = MAX('Table'[Model)
  VAR __Color = MAX('Table'[Color)
  VAR __UnitsSold = MAX('Table'[Units Sold])
RETURN
  IF(__Period = "P1", BLANK(), __UnitsSold - MAXX(FILTER(ALL('Table'),__Period = "P1" && [Model] = __Model && [Color] = __Color),[Units Sold]))