topic balance from 2 tables in Desktop
https://community.powerbi.com/t5/Desktop/balance-from-2-tables/m-p/313693#M139153
<P>Hi,</P><P>I have 2 tables from separate database for the same fields structure.</P><P>We used the old database during 2016 and prior and then we moved to the new database for 2017 transactions and move some balances of 2016 to this new database too.</P><P>This is applied for some entities.</P><P>While for some other entities, we still use the old database until today, which means all transactions until today (2017) is available there.</P><P>I need to create a table (& graph) to combine these 2 tables and show the comparison of (accumulated) balance of 2017 and 2016 by month, which can be filtered by entity and by other categories.</P><P>Need help how I can create this, please.</P><P> </P><P>This is some data samples</P><P>Table #1: Old database</P><TABLE><TBODY><TR><TD>Date</TD><TD>Entity</TD><TD>Category 1</TD><TD>Category 2</TD><TD>Amount</TD></TR><TR><TD>31-Dec-15</TD><TD>A</TD><TD> </TD><TD> </TD><TD>1000</TD></TR><TR><TD>2-Jan-16</TD><TD>A</TD><TD> </TD><TD> </TD><TD>500</TD></TR><TR><TD>15-Feb-16</TD><TD>A</TD><TD> </TD><TD> </TD><TD>900</TD></TR><TR><TD>23-May-16</TD><TD>A</TD><TD> </TD><TD> </TD><TD>400</TD></TR><TR><TD>4-Aug-16</TD><TD>A</TD><TD> </TD><TD> </TD><TD>1100</TD></TR><TR><TD>20-Sep-16</TD><TD>A</TD><TD> </TD><TD> </TD><TD>700</TD></TR><TR><TD>27-Nov-16</TD><TD>A</TD><TD> </TD><TD> </TD><TD>600</TD></TR><TR><TD>5-Dec-16</TD><TD>A</TD><TD> </TD><TD> </TD><TD>1200</TD></TR><TR><TD>31-Dec-15</TD><TD>B</TD><TD> </TD><TD> </TD><TD>70</TD></TR><TR><TD>15-Jan-16</TD><TD>B</TD><TD> </TD><TD> </TD><TD>100</TD></TR><TR><TD>4-Mar-16</TD><TD>B</TD><TD> </TD><TD> </TD><TD>50</TD></TR><TR><TD>25-Jun-16</TD><TD>B</TD><TD> </TD><TD> </TD><TD>90</TD></TR><TR><TD>30-Aug-16</TD><TD>B</TD><TD> </TD><TD> </TD><TD>80</TD></TR><TR><TD>27-Oct-16</TD><TD>B</TD><TD> </TD><TD> </TD><TD>30</TD></TR><TR><TD>12-Dec-16</TD><TD>B</TD><TD> </TD><TD> </TD><TD>100</TD></TR><TR><TD>31-Dec-16</TD><TD>B</TD><TD> </TD><TD> </TD><TD>25</TD></TR><TR><TD>2-Jan-17</TD><TD>B</TD><TD> </TD><TD> </TD><TD>63</TD></TR><TR><TD>15-Feb-17</TD><TD>B</TD><TD> </TD><TD> </TD><TD>37</TD></TR><TR><TD>5-Apr-17</TD><TD>B</TD><TD> </TD><TD> </TD><TD>88</TD></TR><TR><TD>24-Jun-17</TD><TD>B</TD><TD> </TD><TD> </TD><TD>52</TD></TR><TR><TD>30-Jul-17</TD><TD>B</TD><TD> </TD><TD> </TD><TD>91</TD></TR></TBODY></TABLE><P> </P><P>Table #2: New database</P><TABLE><TBODY><TR><TD>Date</TD><TD>Entity</TD><TD>Category 1</TD><TD>Category 2</TD><TD>Amount</TD></TR><TR><TD>31-Dec-16</TD><TD>A</TD><TD> </TD><TD> </TD><TD>6400</TD></TR><TR><TD>31-Dec-16</TD><TD>A</TD><TD> </TD><TD> </TD><TD>100</TD></TR><TR><TD>12-Jan-17</TD><TD>A</TD><TD> </TD><TD> </TD><TD>760</TD></TR><TR><TD>6-Feb-17</TD><TD>A</TD><TD> </TD><TD> </TD><TD>950</TD></TR><TR><TD>20-Mar-17</TD><TD>A</TD><TD> </TD><TD> </TD><TD>430</TD></TR><TR><TD>15-Jun-17</TD><TD>A</TD><TD> </TD><TD> </TD><TD>820</TD></TR><TR><TD>30-Oct-17</TD><TD>A</TD><TD> </TD><TD> </TD><TD>970</TD></TR></TBODY></TABLE><P> </P><P>And this is the result I expect</P><P>If Entity = A</P><TABLE><TBODY><TR><TD>Month</TD><TD>2016</TD><TD>2017</TD></TR><TR><TD>Jan</TD><TD>1500</TD><TD>7260</TD></TR><TR><TD>Feb</TD><TD>2400</TD><TD>8210</TD></TR><TR><TD>Mar</TD><TD>2400</TD><TD>8640</TD></TR><TR><TD>Apr</TD><TD>2400</TD><TD>8640</TD></TR><TR><TD>May</TD><TD>2800</TD><TD>8640</TD></TR><TR><TD>Jun</TD><TD>2800</TD><TD>9460</TD></TR><TR><TD>Jul</TD><TD>2800</TD><TD>9460</TD></TR><TR><TD>Aug</TD><TD>3900</TD><TD>9460</TD></TR><TR><TD>Sep</TD><TD>4600</TD><TD>9460</TD></TR><TR><TD>Oct</TD><TD>4600</TD><TD>10430</TD></TR><TR><TD>Nov</TD><TD>5200</TD><TD>10430</TD></TR><TR><TD>Dec</TD><TD>6500</TD><TD> </TD></TR></TBODY></TABLE><P> </P><P>If Entity = B</P><TABLE><TBODY><TR><TD>Month</TD><TD>2016</TD><TD>2017</TD></TR><TR><TD>Jan</TD><TD>170</TD><TD>608</TD></TR><TR><TD>Feb</TD><TD>170</TD><TD>645</TD></TR><TR><TD>Mar</TD><TD>220</TD><TD>645</TD></TR><TR><TD>Apr</TD><TD>220</TD><TD>733</TD></TR><TR><TD>May</TD><TD>220</TD><TD>733</TD></TR><TR><TD>Jun</TD><TD>310</TD><TD>785</TD></TR><TR><TD>Jul</TD><TD>310</TD><TD>876</TD></TR><TR><TD>Aug</TD><TD>390</TD><TD>876</TD></TR><TR><TD>Sep</TD><TD>390</TD><TD>876</TD></TR><TR><TD>Oct</TD><TD>420</TD><TD>876</TD></TR><TR><TD>Nov</TD><TD>420</TD><TD>876</TD></TR><TR><TD>Dec</TD><TD>545</TD><TD> </TD></TR></TBODY></TABLE>Wed, 29 Nov 2017 10:43:44 GMTRMV2017-11-29T10:43:44Zbalance from 2 tables
https://community.powerbi.com/t5/Desktop/balance-from-2-tables/m-p/313693#M139153
<P>Hi,</P><P>I have 2 tables from separate database for the same fields structure.</P><P>We used the old database during 2016 and prior and then we moved to the new database for 2017 transactions and move some balances of 2016 to this new database too.</P><P>This is applied for some entities.</P><P>While for some other entities, we still use the old database until today, which means all transactions until today (2017) is available there.</P><P>I need to create a table (& graph) to combine these 2 tables and show the comparison of (accumulated) balance of 2017 and 2016 by month, which can be filtered by entity and by other categories.</P><P>Need help how I can create this, please.</P><P> </P><P>This is some data samples</P><P>Table #1: Old database</P><TABLE><TBODY><TR><TD>Date</TD><TD>Entity</TD><TD>Category 1</TD><TD>Category 2</TD><TD>Amount</TD></TR><TR><TD>31-Dec-15</TD><TD>A</TD><TD> </TD><TD> </TD><TD>1000</TD></TR><TR><TD>2-Jan-16</TD><TD>A</TD><TD> </TD><TD> </TD><TD>500</TD></TR><TR><TD>15-Feb-16</TD><TD>A</TD><TD> </TD><TD> </TD><TD>900</TD></TR><TR><TD>23-May-16</TD><TD>A</TD><TD> </TD><TD> </TD><TD>400</TD></TR><TR><TD>4-Aug-16</TD><TD>A</TD><TD> </TD><TD> </TD><TD>1100</TD></TR><TR><TD>20-Sep-16</TD><TD>A</TD><TD> </TD><TD> </TD><TD>700</TD></TR><TR><TD>27-Nov-16</TD><TD>A</TD><TD> </TD><TD> </TD><TD>600</TD></TR><TR><TD>5-Dec-16</TD><TD>A</TD><TD> </TD><TD> </TD><TD>1200</TD></TR><TR><TD>31-Dec-15</TD><TD>B</TD><TD> </TD><TD> </TD><TD>70</TD></TR><TR><TD>15-Jan-16</TD><TD>B</TD><TD> </TD><TD> </TD><TD>100</TD></TR><TR><TD>4-Mar-16</TD><TD>B</TD><TD> </TD><TD> </TD><TD>50</TD></TR><TR><TD>25-Jun-16</TD><TD>B</TD><TD> </TD><TD> </TD><TD>90</TD></TR><TR><TD>30-Aug-16</TD><TD>B</TD><TD> </TD><TD> </TD><TD>80</TD></TR><TR><TD>27-Oct-16</TD><TD>B</TD><TD> </TD><TD> </TD><TD>30</TD></TR><TR><TD>12-Dec-16</TD><TD>B</TD><TD> </TD><TD> </TD><TD>100</TD></TR><TR><TD>31-Dec-16</TD><TD>B</TD><TD> </TD><TD> </TD><TD>25</TD></TR><TR><TD>2-Jan-17</TD><TD>B</TD><TD> </TD><TD> </TD><TD>63</TD></TR><TR><TD>15-Feb-17</TD><TD>B</TD><TD> </TD><TD> </TD><TD>37</TD></TR><TR><TD>5-Apr-17</TD><TD>B</TD><TD> </TD><TD> </TD><TD>88</TD></TR><TR><TD>24-Jun-17</TD><TD>B</TD><TD> </TD><TD> </TD><TD>52</TD></TR><TR><TD>30-Jul-17</TD><TD>B</TD><TD> </TD><TD> </TD><TD>91</TD></TR></TBODY></TABLE><P> </P><P>Table #2: New database</P><TABLE><TBODY><TR><TD>Date</TD><TD>Entity</TD><TD>Category 1</TD><TD>Category 2</TD><TD>Amount</TD></TR><TR><TD>31-Dec-16</TD><TD>A</TD><TD> </TD><TD> </TD><TD>6400</TD></TR><TR><TD>31-Dec-16</TD><TD>A</TD><TD> </TD><TD> </TD><TD>100</TD></TR><TR><TD>12-Jan-17</TD><TD>A</TD><TD> </TD><TD> </TD><TD>760</TD></TR><TR><TD>6-Feb-17</TD><TD>A</TD><TD> </TD><TD> </TD><TD>950</TD></TR><TR><TD>20-Mar-17</TD><TD>A</TD><TD> </TD><TD> </TD><TD>430</TD></TR><TR><TD>15-Jun-17</TD><TD>A</TD><TD> </TD><TD> </TD><TD>820</TD></TR><TR><TD>30-Oct-17</TD><TD>A</TD><TD> </TD><TD> </TD><TD>970</TD></TR></TBODY></TABLE><P> </P><P>And this is the result I expect</P><P>If Entity = A</P><TABLE><TBODY><TR><TD>Month</TD><TD>2016</TD><TD>2017</TD></TR><TR><TD>Jan</TD><TD>1500</TD><TD>7260</TD></TR><TR><TD>Feb</TD><TD>2400</TD><TD>8210</TD></TR><TR><TD>Mar</TD><TD>2400</TD><TD>8640</TD></TR><TR><TD>Apr</TD><TD>2400</TD><TD>8640</TD></TR><TR><TD>May</TD><TD>2800</TD><TD>8640</TD></TR><TR><TD>Jun</TD><TD>2800</TD><TD>9460</TD></TR><TR><TD>Jul</TD><TD>2800</TD><TD>9460</TD></TR><TR><TD>Aug</TD><TD>3900</TD><TD>9460</TD></TR><TR><TD>Sep</TD><TD>4600</TD><TD>9460</TD></TR><TR><TD>Oct</TD><TD>4600</TD><TD>10430</TD></TR><TR><TD>Nov</TD><TD>5200</TD><TD>10430</TD></TR><TR><TD>Dec</TD><TD>6500</TD><TD> </TD></TR></TBODY></TABLE><P> </P><P>If Entity = B</P><TABLE><TBODY><TR><TD>Month</TD><TD>2016</TD><TD>2017</TD></TR><TR><TD>Jan</TD><TD>170</TD><TD>608</TD></TR><TR><TD>Feb</TD><TD>170</TD><TD>645</TD></TR><TR><TD>Mar</TD><TD>220</TD><TD>645</TD></TR><TR><TD>Apr</TD><TD>220</TD><TD>733</TD></TR><TR><TD>May</TD><TD>220</TD><TD>733</TD></TR><TR><TD>Jun</TD><TD>310</TD><TD>785</TD></TR><TR><TD>Jul</TD><TD>310</TD><TD>876</TD></TR><TR><TD>Aug</TD><TD>390</TD><TD>876</TD></TR><TR><TD>Sep</TD><TD>390</TD><TD>876</TD></TR><TR><TD>Oct</TD><TD>420</TD><TD>876</TD></TR><TR><TD>Nov</TD><TD>420</TD><TD>876</TD></TR><TR><TD>Dec</TD><TD>545</TD><TD> </TD></TR></TBODY></TABLE>Wed, 29 Nov 2017 10:43:44 GMThttps://community.powerbi.com/t5/Desktop/balance-from-2-tables/m-p/313693#M139153RMV2017-11-29T10:43:44ZRe: balance from 2 tables
https://community.powerbi.com/t5/Desktop/balance-from-2-tables/m-p/313743#M139176
<P>Hi <LI-USER uid="26838"></LI-USER></P><P> </P><P>Create a New Table from the Modelling Tab</P><P> </P><PRE>New Table =
UNION ( OldDatabase, NewDatabase )</PRE><P>Now you can use a Matrix Visual using this Table.. Put Months in Rows...Year in Columns...Amount in Values</P>Wed, 29 Nov 2017 11:37:34 GMThttps://community.powerbi.com/t5/Desktop/balance-from-2-tables/m-p/313743#M139176Zubair_Muhammad2017-11-29T11:37:34ZRe: balance from 2 tables
https://community.powerbi.com/t5/Desktop/balance-from-2-tables/m-p/314279#M139502
<P>Hi <LI-USER uid="42973"></LI-USER>,</P><P> </P><P>Thanks for your advise.</P><P>The thing is for entity A data, I only need transactional data until Nov 2016 to calculate the accumulated balance.</P><P>Then Dec 2016 will take the total amount from the new database. The following monts ahead use accumulated balance of the transactional data from the new database.</P><P>While for entity B, the accumulated balance is all from the old database.</P><P>Any advise, please?</P>Thu, 30 Nov 2017 02:53:57 GMThttps://community.powerbi.com/t5/Desktop/balance-from-2-tables/m-p/314279#M139502RMV2017-11-30T02:53:57ZRe: balance from 2 tables
https://community.powerbi.com/t5/Desktop/balance-from-2-tables/m-p/314337#M139529
<P>HI <LI-USER uid="26838"></LI-USER></P><P> </P><P>In that case</P><P> </P><PRE>New Table =
VAR RowstoExclude =
FILTER (
OldDatabase,
OldDatabase[Entity] = "A"
&& OldDatabase[Date] > DATE ( 2016, 11, 30 )
)
RETURN
UNION ( EXCEPT ( OldDatabase, RowstoExclude ), NewDatabase )</PRE>Thu, 30 Nov 2017 06:05:24 GMThttps://community.powerbi.com/t5/Desktop/balance-from-2-tables/m-p/314337#M139529Zubair_Muhammad2017-11-30T06:05:24ZRe: balance from 2 tables
https://community.powerbi.com/t5/Desktop/balance-from-2-tables/m-p/320119#M142465
<P>Hi <LI-USER uid="42973"></LI-USER>,</P><P> </P><P>Got it! Thanks for your help.</P>Fri, 08 Dec 2017 10:37:20 GMThttps://community.powerbi.com/t5/Desktop/balance-from-2-tables/m-p/320119#M142465RMV2017-12-08T10:37:20Z