Measure calculating variance between ID

[Whoule]

Hi,

Here is my table.

IDProcessing CentreTeamCategorySub-CategoryDateNumber Oustanding ItemsDollar Value

2023402	x	yy	a		2023-04-15	80	$          1,100.00
2023402	x	yy	a		2023-04-15	80	$        31,100.00
2023402	x	yy	a		2023-04-15	1	$              800.00
2023402	x	yy	a		2023-04-15
2023402	x	yy	a		2023-04-15	16	$          4,400.00
2023402	x	yy	a		2023-04-15	5
2023402	x	yy	b	High	2023-04-15	3727
2023402	x	yy	b	Medium	2023-04-15	416
2023402	x	yy	b	Low	2023-04-15	382
2023402	x	yy	b	Nil	2023-04-15	921
2023402	x	yy	b	Non-Payment	2023-04-15	217	$  9,181,600.00
2023402	x	yy	b	Converted	2023-04-15	280
2023402	x	yy	b	Refunds	2023-04-15
2023501	x	yy	a		2023-05-01	1	$          4,443.29
2023501	x	yy	a		2023-05-01	21	$          5,683.29
2023501	x	yy	a		2023-05-01	2	$              800.00
2023501	x	yy	a		2023-05-01	7	$              800.00
2023501	x	yy	a		2023-05-01	15	$          6,800.00
2023501	x	yy	a		2023-05-01	5
2023501	x	yy	b	High	2023-05-01	1550
2023501	x	yy	b	Medium	2023-05-01	500
2023501	x	yy	b	Low	2023-05-01	120
2023501	x	yy	b	Nil	2023-05-01	1230
2023501	x	yy	b	Non-Payment	2023-05-01	100	$      915,200.00
2023501	x	yy	b	Converted	2023-05-01	270
2023501	x	yy	b	Refunds	2023-05-01
2023502	x	yy	a		2023-05-15	500	$        43,400.00
2023502	x	yy	a		2023-05-15	100	$        43,400.00
2023502	x	yy	a		2023-05-15	4	$          2,600.00
2023502	x	yy	a		2023-05-15	1	$              800.00
2023502	x	yy	a		2023-05-15	15	$          7,500.00
2023502	x	yy	a		2023-05-15	7
2023502	x	yy	b	High	2023-05-15	3000
2023502	x	yy	b	Medium	2023-05-15	256
2023502	x	yy	b	Low	2023-05-15	800
2023502	x	yy	b	Nil	2023-05-15	1200
2023502	x	yy	b	Non-Payment	2023-05-15	229	$  1,956,300.00
2023502	x	yy	b	Converted	2023-05-15	249
2023502	x	yy	b	Refunds	2023-05-15

I am looking for help to build a measure that would calculate the variance of outstanding items between the latest period (either use date column or the ID column) and the period just before the latest. In this example, it would be the variance between period with ID 2023502 and 2023501.

Thank you.

[amitchandak · Accepted solution]

@Whoule , try a measure like

Variance Items =
VAR _lastest = MAX('Table'[ID])
VAR _previous= CALCULATE(MAX('Table'[ID]), 'Table'[ID] < _lastest)

VAR _latest_out = CALCULATE(SUM('Table'[Number Oustanding Items]), 'Table'[ID] = _lastest)
VAR _previous_out = CALCULATE(SUM('Table'[Number Oustanding Items]), 'Table'[ID] = PreviousPeriodID)

RETURN
IF(ISBLANK(_latest_out) || ISBLANK(_previous_out), BLANK(),
_latest_out - _previous_out)