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. 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

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

1 Reply

  • 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