Calculate variance dynamically between two dates even part is replaced

[tangutoori]

Hi Team,

we have a requirment to calculate variance between two dates (slicer to select date1 and date2). Bridge list contains Old index and New index -- which means old index is a Index on Date1 and New Index is replaced Name.

even though they looks different but they are same. we should do variance between 

INDIA|PUNJAB|A9867|3

INDIA|PUNJAB|A9898|3

Bridge List

Old Index	New Index
INDIA|PUNJAB|A9867|3	INDIA|PUNJAB|A9898|3

SOURCE

COUNTRY	STATE	Name	ID	Colour	Value	DATE	INDEX
INDIA	DELHI	Y1864	1	GREEN	60	27-Jan-23	INDIA|DELHI|Y1864|1
INDIA	GOA	Z2345	2	BLUE	70	27-Jan-23	INDIA|GOA|Z2345|2
INDIA	PUNJAB	A9867	3	GREEN	-190	27-Jan-23	INDIA|PUNJAB|A9867|3
INDIA	DELHI	Y1864	1	GREEN	80	31-Jan-23	INDIA|DELHI|Y1864|1
INDIA	GOA	Z2345	2	BLUE	70	31-Jan-23	INDIA|GOA|Z2345|2
INDIA	PUNJAB	A9867	3	GREEN	-150	31-Jan-23	INDIA|PUNJAB|A9867|3
INDIA	PUNJAB	C1234	4	GREEN	-100	31-Jan-23	INDIA|PUNJAB|C1234|4
INDIA	DELHI	Y1864	1	GREEN	60	2-Feb-23	INDIA|DELHI|Y1864|1
INDIA	GOA	Z2345	2	BLUE	70	2-Feb-23	INDIA|GOA|Z2345|2
INDIA	PUNJAB	A9898	3	GREEN	-200	2-Feb-23	INDIA|PUNJAB|A9898|3
INDIA	PUNJAB	C1234	4	GREEN	-100	2-Feb-23	INDIA|PUNJAB|C1234|4

OUTPUT1

Select slicer1 & slcier 2 (Date1 27-Jan-2023 & Date2 31-Jan-2023)

INDEX ON DATE1	INDEX ON DATE2	COLOUR ON DATE1	COLOUR ON DATE2	VALUE ON DATE1	VALUE ON DATE2	DELTA
INDIA|DELHI|Y1864|1	INDIA|DELHI|Y1864|1	GREEN	GREEN	60	80	20
INDIA|GOA|Z2345|2	INDIA|GOA|Z2345|2	BLUE	BLUE	70	70	0
INDIA|PUNJAB|A9867|3	INDIA|PUNJAB|A9867|3	GREEN	GREEN	-190	-150	40
	INDIA|PUNJAB|C1234|4		GREEN		-100	-100

OUTPUT2

Select slicer1 & slcier 2 (Date1 = 31-Jan-2023 & Date2 2-Feb-2023)

On 2-Feb-23 INDIA|PUNJAB|A9867|3 is replaced as INDIA|PUNJAB|A9898|3 so PBI should identify the replacement by referign brdigelist tablew

INDEX ON DATE1	INDEX ON DATE2	COLOUR ON DATE1	COLOUR ON DATE2	VALUE ON DATE1	VALUE ON DATE2	DELTA
INDIA|DELHI|Y1864|1	INDIA|DELHI|Y1864|1	GREEN	GREEN	60	60	0
INDIA|GOA|Z2345|2	INDIA|GOA|Z2345|2	BLUE	BLUE	70	70	0
INDIA|PUNJAB|A9867|3	INDIA|PUNJAB|A9898|3	GREEN	GREEN	-190	-200	-10
	INDIA|PUNJAB|C1234|4		GREEN		-100	-100

Regards,

Reddy.

[lbendlin]

On 2-Feb-23 INDIA|PUNJAB|A9867|3 is replaced as INDIA|PUNJAB|A9898|3

That is not clear from the sample data.  It could equally be replaced by INDIA|PUNJAB|C1234|4.  You will want to remove that ambiguity before proceeding.