cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

balance from 2 tables

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

DateEntityCategory 1Category 2Amount
31-Dec-15A  1000
2-Jan-16A  500
15-Feb-16A  900
23-May-16A  400
4-Aug-16A  1100
20-Sep-16A  700
27-Nov-16A  600
5-Dec-16A  1200
31-Dec-15B  70
15-Jan-16B  100
4-Mar-16B  50
25-Jun-16B  90
30-Aug-16B  80
27-Oct-16B  30
12-Dec-16B  100
31-Dec-16B  25
2-Jan-17B  63
15-Feb-17B  37
5-Apr-17B  88
24-Jun-17B  52
30-Jul-17B  91

 

Table #2: New database

DateEntityCategory 1Category 2Amount
31-Dec-16A  6400
31-Dec-16A  100
12-Jan-17A  760
6-Feb-17A  950
20-Mar-17A  430
15-Jun-17A  820
30-Oct-17A  970

 

And this is the result I expect

If Entity = A

Month20162017
Jan15007260
Feb24008210
Mar24008640
Apr24008640
May28008640
Jun28009460
Jul28009460
Aug39009460
Sep46009460
Oct460010430
Nov520010430
Dec6500 

 

If Entity = B

Month20162017
Jan170608
Feb170645
Mar220645
Apr220733
May220733
Jun310785
Jul310876
Aug390876
Sep390876
Oct420876
Nov420876
Dec545 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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 )
Regards,
Zubair


View solution in original post

4 REPLIES 4
Highlighted
Super User I
Super User I

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

Regards,
Zubair


Highlighted

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?

Highlighted

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 )
Regards,
Zubair


View solution in original post

Highlighted

Hi @Zubair_Muhammad,

 

Got it! Thanks for your help.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors