Column for Finding Difference in Matrix

[TotunG]

Hey, 

I've looked through the forum examples and not sure if this is possible as everyone resorts to using a Table with columns but this means some of my data granularity is excluded e.g. Categories / Sub-Categories.  I essentially need a measure that uses row context and filter context to find the difference between values and put this in a Matrix. This is by taking the latest period values and subtracting the next latest period values (period before MAX period).

sample PBI attached and photo of results:

https://drive.google.com/file/d/19KDNuA7CRElc-O1H8WwschGNoU29qUBv/view?usp=sharing

[johnt75 · Accepted solution]

You can create a measure like

Diff = 
VAR LatestPeriod =
	CALCULATE(
		MAX( 'Period Ref'[Period Ref] ),
		ALLSELECTED( 'Period Ref' )
	)
VAR CurrentPeriod = MAX( 'Period Ref'[Period Ref])
VAR PrevPeriod =
	CALCULATE(
		MAX( 'Period Ref'[Period Ref] ),
		ALLSELECTED( 'Period Ref' ),
		'Period Ref'[Period Ref] < LatestPeriod
	)
VAR LatestValue =
	CALCULATE(
		[Total Value],
		REMOVEFILTERS( 'Period Ref' ),
		'Period Ref'[Period Ref] = LatestPeriod
	)
VAR PrevValue =
	CALCULATE(
		[Total Value],
		REMOVEFILTERS( 'Period Ref' ),
		'Period Ref'[Period Ref] = PrevPeriod
	)
RETURN
	IF(
		CurrentPeriod = LatestPeriod,
		LatestValue - PrevValue
	)

and add that to the values of the matrix