cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.