Forum Discussion

TotunG's avatar
TotunG
Resolver I
3 years ago
Solved

Column for Finding Difference in Matrix

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. Categor...
Need Help
Tips and Tricks
Tutorial Requests
  • johnt75's avatar
    johnt75
    3 years ago

    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

johnt75's avatar
johnt75
Super User
3 years ago

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