Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
tangutoori
Helper III
Helper III

Calculate variance dynamically between two dates even part is replaced

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.

1 REPLY 1
lbendlin
Super User
Super User

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.

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.