Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have 2 tables from separate database for the same fields structure.
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.
This is applied for some entities.
While for some other entities, we still use the old database until today, which means all transactions until today (2017) is available there.
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.
Need help how I can create this, please.
This is some data samples
Table #1: Old database
Date | Entity | Category 1 | Category 2 | Amount |
31-Dec-15 | A | 1000 | ||
2-Jan-16 | A | 500 | ||
15-Feb-16 | A | 900 | ||
23-May-16 | A | 400 | ||
4-Aug-16 | A | 1100 | ||
20-Sep-16 | A | 700 | ||
27-Nov-16 | A | 600 | ||
5-Dec-16 | A | 1200 | ||
31-Dec-15 | B | 70 | ||
15-Jan-16 | B | 100 | ||
4-Mar-16 | B | 50 | ||
25-Jun-16 | B | 90 | ||
30-Aug-16 | B | 80 | ||
27-Oct-16 | B | 30 | ||
12-Dec-16 | B | 100 | ||
31-Dec-16 | B | 25 | ||
2-Jan-17 | B | 63 | ||
15-Feb-17 | B | 37 | ||
5-Apr-17 | B | 88 | ||
24-Jun-17 | B | 52 | ||
30-Jul-17 | B | 91 |
Table #2: New database
Date | Entity | Category 1 | Category 2 | Amount |
31-Dec-16 | A | 6400 | ||
31-Dec-16 | A | 100 | ||
12-Jan-17 | A | 760 | ||
6-Feb-17 | A | 950 | ||
20-Mar-17 | A | 430 | ||
15-Jun-17 | A | 820 | ||
30-Oct-17 | A | 970 |
And this is the result I expect
If Entity = A
Month | 2016 | 2017 |
Jan | 1500 | 7260 |
Feb | 2400 | 8210 |
Mar | 2400 | 8640 |
Apr | 2400 | 8640 |
May | 2800 | 8640 |
Jun | 2800 | 9460 |
Jul | 2800 | 9460 |
Aug | 3900 | 9460 |
Sep | 4600 | 9460 |
Oct | 4600 | 10430 |
Nov | 5200 | 10430 |
Dec | 6500 |
If Entity = B
Month | 2016 | 2017 |
Jan | 170 | 608 |
Feb | 170 | 645 |
Mar | 220 | 645 |
Apr | 220 | 733 |
May | 220 | 733 |
Jun | 310 | 785 |
Jul | 310 | 876 |
Aug | 390 | 876 |
Sep | 390 | 876 |
Oct | 420 | 876 |
Nov | 420 | 876 |
Dec | 545 |
Solved! Go to Solution.
HI @RMV
In that case
New Table = VAR RowstoExclude = FILTER ( OldDatabase, OldDatabase[Entity] = "A" && OldDatabase[Date] > DATE ( 2016, 11, 30 ) ) RETURN UNION ( EXCEPT ( OldDatabase, RowstoExclude ), NewDatabase )
Hi @RMV
Create a New Table from the Modelling Tab
New Table = UNION ( OldDatabase, NewDatabase )
Now you can use a Matrix Visual using this Table.. Put Months in Rows...Year in Columns...Amount in Values
Hi @Zubair_Muhammad,
Thanks for your advise.
The thing is for entity A data, I only need transactional data until Nov 2016 to calculate the accumulated balance.
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.
While for entity B, the accumulated balance is all from the old database.
Any advise, please?
HI @RMV
In that case
New Table = VAR RowstoExclude = FILTER ( OldDatabase, OldDatabase[Entity] = "A" && OldDatabase[Date] > DATE ( 2016, 11, 30 ) ) RETURN UNION ( EXCEPT ( OldDatabase, RowstoExclude ), NewDatabase )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |