topic Calculate variance dynamically between two dates even part is replaced in Report Server https://community.fabric.microsoft.com/t5/Report-Server/Calculate-variance-dynamically-between-two-dates-even-part-is/m-p/3530457#M31781 <P>Hi Team,</P><P>&nbsp;</P><P>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.</P><P>&nbsp;</P><P>even though they looks different but they are same. we should do variance between&nbsp;</P><TABLE><TBODY><TR><TD><P>INDIA|PUNJAB|A9867|3</P></TD><TD><P>INDIA|PUNJAB|A9898|3</P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P><P>Bridge List</P><TABLE><TBODY><TR><TD><P><STRONG>Old Index</STRONG></P></TD><TD><P><STRONG>New Index</STRONG></P></TD></TR><TR><TD><P>INDIA|PUNJAB|A9867|3</P></TD><TD><P>INDIA|PUNJAB|A9898|3</P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>&nbsp;</P><P><STRONG>SOURCE</STRONG></P><TABLE><TBODY><TR><TD><P>COUNTRY</P></TD><TD><P>STATE</P></TD><TD><P>Name</P></TD><TD><P>ID</P></TD><TD><P>Colour</P></TD><TD><P>Value</P></TD><TD><P>DATE</P></TD><TD><P>INDEX</P></TD></TR><TR><TD><P>INDIA</P></TD><TD><P>DELHI</P></TD><TD><P>Y1864</P></TD><TD><P>1</P></TD><TD><P>GREEN</P></TD><TD><P>60</P></TD><TD><P>27-Jan-23</P></TD><TD><P>INDIA|DELHI|Y1864|1</P></TD></TR><TR><TD><P>INDIA</P></TD><TD><P>GOA</P></TD><TD><P>Z2345</P></TD><TD><P>2</P></TD><TD><P>BLUE</P></TD><TD><P>70</P></TD><TD><P>27-Jan-23</P></TD><TD><P>INDIA|GOA|Z2345|2</P></TD></TR><TR><TD><P>INDIA</P></TD><TD><P>PUNJAB</P></TD><TD><P>A9867</P></TD><TD><P>3</P></TD><TD><P>GREEN</P></TD><TD><P>-190</P></TD><TD><P>27-Jan-23</P></TD><TD><P>INDIA|PUNJAB|A9867|3</P></TD></TR><TR><TD><P>INDIA</P></TD><TD><P>DELHI</P></TD><TD><P>Y1864</P></TD><TD><P>1</P></TD><TD><P>GREEN</P></TD><TD><P>80</P></TD><TD><P>31-Jan-23</P></TD><TD><P>INDIA|DELHI|Y1864|1</P></TD></TR><TR><TD><P>INDIA</P></TD><TD><P>GOA</P></TD><TD><P>Z2345</P></TD><TD><P>2</P></TD><TD><P>BLUE</P></TD><TD><P>70</P></TD><TD><P>31-Jan-23</P></TD><TD><P>INDIA|GOA|Z2345|2</P></TD></TR><TR><TD><P>INDIA</P></TD><TD><P>PUNJAB</P></TD><TD><P>A9867</P></TD><TD><P>3</P></TD><TD><P>GREEN</P></TD><TD><P>-150</P></TD><TD><P>31-Jan-23</P></TD><TD><P>INDIA|PUNJAB|A9867|3</P></TD></TR><TR><TD><P>INDIA</P></TD><TD><P>PUNJAB</P></TD><TD><P>C1234</P></TD><TD><P>4</P></TD><TD><P>GREEN</P></TD><TD><P>-100</P></TD><TD><P>31-Jan-23</P></TD><TD><P>INDIA|PUNJAB|C1234|4</P></TD></TR><TR><TD><P>INDIA</P></TD><TD><P>DELHI</P></TD><TD><P>Y1864</P></TD><TD><P>1</P></TD><TD><P>GREEN</P></TD><TD><P>60</P></TD><TD><P>2-Feb-23</P></TD><TD><P>INDIA|DELHI|Y1864|1</P></TD></TR><TR><TD><P>INDIA</P></TD><TD><P>GOA</P></TD><TD><P>Z2345</P></TD><TD><P>2</P></TD><TD><P>BLUE</P></TD><TD><P>70</P></TD><TD><P>2-Feb-23</P></TD><TD><P>INDIA|GOA|Z2345|2</P></TD></TR><TR><TD><P>INDIA</P></TD><TD><P>PUNJAB</P></TD><TD><P>A9898</P></TD><TD><P>3</P></TD><TD><P>GREEN</P></TD><TD><P>-200</P></TD><TD><P>2-Feb-23</P></TD><TD><P>INDIA|PUNJAB|A9898|3</P></TD></TR><TR><TD><P>INDIA</P></TD><TD><P>PUNJAB</P></TD><TD><P>C1234</P></TD><TD><P>4</P></TD><TD><P>GREEN</P></TD><TD><P>-100</P></TD><TD><P>2-Feb-23</P></TD><TD><P>INDIA|PUNJAB|C1234|4</P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>OUTPUT1</P><P>Select slicer1 &amp; slcier 2 (Date1 27-Jan-2023 &amp; Date2 31-Jan-2023)</P><TABLE><TBODY><TR><TD><P><STRONG>INDEX ON DATE1</STRONG></P></TD><TD><P><STRONG>INDEX ON DATE2</STRONG></P></TD><TD><P><STRONG>COLOUR ON DATE1</STRONG></P></TD><TD><P><STRONG>COLOUR ON DATE2</STRONG></P></TD><TD><P><STRONG>VALUE ON DATE1</STRONG></P></TD><TD><P><STRONG>VALUE ON DATE2</STRONG></P></TD><TD><P><STRONG>DELTA</STRONG></P></TD></TR><TR><TD><P>INDIA|DELHI|Y1864|1</P></TD><TD><P>INDIA|DELHI|Y1864|1</P></TD><TD><P>GREEN</P></TD><TD><P>GREEN</P></TD><TD><P>60</P></TD><TD><P>80</P></TD><TD><P>20</P></TD></TR><TR><TD><P>INDIA|GOA|Z2345|2</P></TD><TD><P>INDIA|GOA|Z2345|2</P></TD><TD><P>BLUE</P></TD><TD><P>BLUE</P></TD><TD><P>70</P></TD><TD><P>70</P></TD><TD><P>0</P></TD></TR><TR><TD><P>INDIA|PUNJAB|A9867|3</P></TD><TD><P>INDIA|PUNJAB|A9867|3</P></TD><TD><P>GREEN</P></TD><TD><P>GREEN</P></TD><TD><P>-190</P></TD><TD><P>-150</P></TD><TD><P>40</P></TD></TR><TR><TD>&nbsp;</TD><TD><P>INDIA|PUNJAB|C1234|4</P></TD><TD>&nbsp;</TD><TD><P>GREEN</P></TD><TD>&nbsp;</TD><TD><P>-100</P></TD><TD><P>-100</P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>OUTPUT2</P><P>Select slicer1 &amp; slcier 2 (Date1 = 31-Jan-2023 &amp; Date2 2-Feb-2023)</P><P>On 2-Feb-23 <STRONG>INDIA|PUNJAB|A9867|3 </STRONG>is replaced as<STRONG> INDIA|PUNJAB|A9898|3 </STRONG>so PBI should identify the replacement by referign brdigelist tablew</P><TABLE><TBODY><TR><TD><P><STRONG>INDEX ON DATE1</STRONG></P></TD><TD><P><STRONG>INDEX ON DATE2</STRONG></P></TD><TD><P><STRONG>COLOUR ON DATE1</STRONG></P></TD><TD><P><STRONG>COLOUR ON DATE2</STRONG></P></TD><TD><P><STRONG>VALUE ON DATE1</STRONG></P></TD><TD><P><STRONG>VALUE ON DATE2</STRONG></P></TD><TD><P><STRONG>DELTA</STRONG></P></TD></TR><TR><TD><P>INDIA|DELHI|Y1864|1</P></TD><TD><P>INDIA|DELHI|Y1864|1</P></TD><TD><P>GREEN</P></TD><TD><P>GREEN</P></TD><TD><P>60</P></TD><TD><P>60</P></TD><TD><P>0</P></TD></TR><TR><TD><P>INDIA|GOA|Z2345|2</P></TD><TD><P>INDIA|GOA|Z2345|2</P></TD><TD><P>BLUE</P></TD><TD><P>BLUE</P></TD><TD><P>70</P></TD><TD><P>70</P></TD><TD><P>0</P></TD></TR><TR><TD><P><STRONG>INDIA|PUNJAB|A9867|3</STRONG></P></TD><TD><P><STRONG>INDIA|PUNJAB|A9898|3</STRONG></P></TD><TD><P>GREEN</P></TD><TD><P>GREEN</P></TD><TD><P>-190</P></TD><TD><P>-200</P></TD><TD><P>-10</P></TD></TR><TR><TD>&nbsp;</TD><TD><P>INDIA|PUNJAB|C1234|4</P></TD><TD>&nbsp;</TD><TD><P>GREEN</P></TD><TD>&nbsp;</TD><TD><P>-100</P></TD><TD><P>-100</P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>&nbsp;</P><P>Regards,</P><P>Reddy.</P> Mon, 13 Nov 2023 09:25:48 GMT tangutoori 2023-11-13T09:25:48Z Calculate variance dynamically between two dates even part is replaced https://community.fabric.microsoft.com/t5/Report-Server/Calculate-variance-dynamically-between-two-dates-even-part-is/m-p/3530457#M31781 <P>Hi Team,</P><P>&nbsp;</P><P>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.</P><P>&nbsp;</P><P>even though they looks different but they are same. we should do variance between&nbsp;</P><TABLE><TBODY><TR><TD><P>INDIA|PUNJAB|A9867|3</P></TD><TD><P>INDIA|PUNJAB|A9898|3</P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P><P>Bridge List</P><TABLE><TBODY><TR><TD><P><STRONG>Old Index</STRONG></P></TD><TD><P><STRONG>New Index</STRONG></P></TD></TR><TR><TD><P>INDIA|PUNJAB|A9867|3</P></TD><TD><P>INDIA|PUNJAB|A9898|3</P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>&nbsp;</P><P><STRONG>SOURCE</STRONG></P><TABLE><TBODY><TR><TD><P>COUNTRY</P></TD><TD><P>STATE</P></TD><TD><P>Name</P></TD><TD><P>ID</P></TD><TD><P>Colour</P></TD><TD><P>Value</P></TD><TD><P>DATE</P></TD><TD><P>INDEX</P></TD></TR><TR><TD><P>INDIA</P></TD><TD><P>DELHI</P></TD><TD><P>Y1864</P></TD><TD><P>1</P></TD><TD><P>GREEN</P></TD><TD><P>60</P></TD><TD><P>27-Jan-23</P></TD><TD><P>INDIA|DELHI|Y1864|1</P></TD></TR><TR><TD><P>INDIA</P></TD><TD><P>GOA</P></TD><TD><P>Z2345</P></TD><TD><P>2</P></TD><TD><P>BLUE</P></TD><TD><P>70</P></TD><TD><P>27-Jan-23</P></TD><TD><P>INDIA|GOA|Z2345|2</P></TD></TR><TR><TD><P>INDIA</P></TD><TD><P>PUNJAB</P></TD><TD><P>A9867</P></TD><TD><P>3</P></TD><TD><P>GREEN</P></TD><TD><P>-190</P></TD><TD><P>27-Jan-23</P></TD><TD><P>INDIA|PUNJAB|A9867|3</P></TD></TR><TR><TD><P>INDIA</P></TD><TD><P>DELHI</P></TD><TD><P>Y1864</P></TD><TD><P>1</P></TD><TD><P>GREEN</P></TD><TD><P>80</P></TD><TD><P>31-Jan-23</P></TD><TD><P>INDIA|DELHI|Y1864|1</P></TD></TR><TR><TD><P>INDIA</P></TD><TD><P>GOA</P></TD><TD><P>Z2345</P></TD><TD><P>2</P></TD><TD><P>BLUE</P></TD><TD><P>70</P></TD><TD><P>31-Jan-23</P></TD><TD><P>INDIA|GOA|Z2345|2</P></TD></TR><TR><TD><P>INDIA</P></TD><TD><P>PUNJAB</P></TD><TD><P>A9867</P></TD><TD><P>3</P></TD><TD><P>GREEN</P></TD><TD><P>-150</P></TD><TD><P>31-Jan-23</P></TD><TD><P>INDIA|PUNJAB|A9867|3</P></TD></TR><TR><TD><P>INDIA</P></TD><TD><P>PUNJAB</P></TD><TD><P>C1234</P></TD><TD><P>4</P></TD><TD><P>GREEN</P></TD><TD><P>-100</P></TD><TD><P>31-Jan-23</P></TD><TD><P>INDIA|PUNJAB|C1234|4</P></TD></TR><TR><TD><P>INDIA</P></TD><TD><P>DELHI</P></TD><TD><P>Y1864</P></TD><TD><P>1</P></TD><TD><P>GREEN</P></TD><TD><P>60</P></TD><TD><P>2-Feb-23</P></TD><TD><P>INDIA|DELHI|Y1864|1</P></TD></TR><TR><TD><P>INDIA</P></TD><TD><P>GOA</P></TD><TD><P>Z2345</P></TD><TD><P>2</P></TD><TD><P>BLUE</P></TD><TD><P>70</P></TD><TD><P>2-Feb-23</P></TD><TD><P>INDIA|GOA|Z2345|2</P></TD></TR><TR><TD><P>INDIA</P></TD><TD><P>PUNJAB</P></TD><TD><P>A9898</P></TD><TD><P>3</P></TD><TD><P>GREEN</P></TD><TD><P>-200</P></TD><TD><P>2-Feb-23</P></TD><TD><P>INDIA|PUNJAB|A9898|3</P></TD></TR><TR><TD><P>INDIA</P></TD><TD><P>PUNJAB</P></TD><TD><P>C1234</P></TD><TD><P>4</P></TD><TD><P>GREEN</P></TD><TD><P>-100</P></TD><TD><P>2-Feb-23</P></TD><TD><P>INDIA|PUNJAB|C1234|4</P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>OUTPUT1</P><P>Select slicer1 &amp; slcier 2 (Date1 27-Jan-2023 &amp; Date2 31-Jan-2023)</P><TABLE><TBODY><TR><TD><P><STRONG>INDEX ON DATE1</STRONG></P></TD><TD><P><STRONG>INDEX ON DATE2</STRONG></P></TD><TD><P><STRONG>COLOUR ON DATE1</STRONG></P></TD><TD><P><STRONG>COLOUR ON DATE2</STRONG></P></TD><TD><P><STRONG>VALUE ON DATE1</STRONG></P></TD><TD><P><STRONG>VALUE ON DATE2</STRONG></P></TD><TD><P><STRONG>DELTA</STRONG></P></TD></TR><TR><TD><P>INDIA|DELHI|Y1864|1</P></TD><TD><P>INDIA|DELHI|Y1864|1</P></TD><TD><P>GREEN</P></TD><TD><P>GREEN</P></TD><TD><P>60</P></TD><TD><P>80</P></TD><TD><P>20</P></TD></TR><TR><TD><P>INDIA|GOA|Z2345|2</P></TD><TD><P>INDIA|GOA|Z2345|2</P></TD><TD><P>BLUE</P></TD><TD><P>BLUE</P></TD><TD><P>70</P></TD><TD><P>70</P></TD><TD><P>0</P></TD></TR><TR><TD><P>INDIA|PUNJAB|A9867|3</P></TD><TD><P>INDIA|PUNJAB|A9867|3</P></TD><TD><P>GREEN</P></TD><TD><P>GREEN</P></TD><TD><P>-190</P></TD><TD><P>-150</P></TD><TD><P>40</P></TD></TR><TR><TD>&nbsp;</TD><TD><P>INDIA|PUNJAB|C1234|4</P></TD><TD>&nbsp;</TD><TD><P>GREEN</P></TD><TD>&nbsp;</TD><TD><P>-100</P></TD><TD><P>-100</P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>OUTPUT2</P><P>Select slicer1 &amp; slcier 2 (Date1 = 31-Jan-2023 &amp; Date2 2-Feb-2023)</P><P>On 2-Feb-23 <STRONG>INDIA|PUNJAB|A9867|3 </STRONG>is replaced as<STRONG> INDIA|PUNJAB|A9898|3 </STRONG>so PBI should identify the replacement by referign brdigelist tablew</P><TABLE><TBODY><TR><TD><P><STRONG>INDEX ON DATE1</STRONG></P></TD><TD><P><STRONG>INDEX ON DATE2</STRONG></P></TD><TD><P><STRONG>COLOUR ON DATE1</STRONG></P></TD><TD><P><STRONG>COLOUR ON DATE2</STRONG></P></TD><TD><P><STRONG>VALUE ON DATE1</STRONG></P></TD><TD><P><STRONG>VALUE ON DATE2</STRONG></P></TD><TD><P><STRONG>DELTA</STRONG></P></TD></TR><TR><TD><P>INDIA|DELHI|Y1864|1</P></TD><TD><P>INDIA|DELHI|Y1864|1</P></TD><TD><P>GREEN</P></TD><TD><P>GREEN</P></TD><TD><P>60</P></TD><TD><P>60</P></TD><TD><P>0</P></TD></TR><TR><TD><P>INDIA|GOA|Z2345|2</P></TD><TD><P>INDIA|GOA|Z2345|2</P></TD><TD><P>BLUE</P></TD><TD><P>BLUE</P></TD><TD><P>70</P></TD><TD><P>70</P></TD><TD><P>0</P></TD></TR><TR><TD><P><STRONG>INDIA|PUNJAB|A9867|3</STRONG></P></TD><TD><P><STRONG>INDIA|PUNJAB|A9898|3</STRONG></P></TD><TD><P>GREEN</P></TD><TD><P>GREEN</P></TD><TD><P>-190</P></TD><TD><P>-200</P></TD><TD><P>-10</P></TD></TR><TR><TD>&nbsp;</TD><TD><P>INDIA|PUNJAB|C1234|4</P></TD><TD>&nbsp;</TD><TD><P>GREEN</P></TD><TD>&nbsp;</TD><TD><P>-100</P></TD><TD><P>-100</P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>&nbsp;</P><P>Regards,</P><P>Reddy.</P> Mon, 13 Nov 2023 09:25:48 GMT https://community.fabric.microsoft.com/t5/Report-Server/Calculate-variance-dynamically-between-two-dates-even-part-is/m-p/3530457#M31781 tangutoori 2023-11-13T09:25:48Z Re: Calculate variance dynamically between two dates even part is replaced https://community.fabric.microsoft.com/t5/Report-Server/Calculate-variance-dynamically-between-two-dates-even-part-is/m-p/3540342#M31843 <LI-CODE lang="markup">On 2-Feb-23 INDIA|PUNJAB|A9867|3 is replaced as INDIA|PUNJAB|A9898|3</LI-CODE> <P>That is not clear from the sample data.&nbsp; It could equally be replaced by&nbsp;<SPAN>INDIA|PUNJAB|C1234|4.&nbsp; You will want to remove that ambiguity before proceeding.</SPAN></P> Sat, 18 Nov 2023 16:38:03 GMT https://community.fabric.microsoft.com/t5/Report-Server/Calculate-variance-dynamically-between-two-dates-even-part-is/m-p/3540342#M31843 lbendlin 2023-11-18T16:38:03Z