balance from 2 tables

[RMV]

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

[Zubair_Muhammad]

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

[RMV]

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?

[Zubair_Muhammad · Accepted 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 )

[RMV]

Hi @Zubair_Muhammad,

Got it! Thanks for your help.