Table Self Join with Two Primary Keys

Hello people,

I want to merge a table with it self using two primary keys. I have tried the Left Join privided in Merge Queries in Power BI but it doesn't give me the outcome that I want.

My table has 4 columns : Date, Account Number (Text), Amount (Decimal Number), Previous Month Date

The merge that I want do is on : LEFT (Previous Month Date) AND (Account Number) MERGE ON (Date) AND (Account Number) RIGHT

I want to keep all the lines in the Previous Month Date column that's why I started with Left Join and some lines of course will be null (zero) after the merge. The other lines will end up having the Amount of Current Date AND Previous Date on the same row which is the outcome that I want to have in the end!

The pic below is the final outcome that I want and the dataset. 

Thanks a lot, I'm open to any question that you might have!

Cheers,

Alex

P.S. Hint that might help you, in python using Pandas the solution looks like that :

table.merge(table, 
            how =  'left',
            left_on = ['Previous Month Date', 'Account Number'],
            right_on = ['Date', 'Account Number'])
  

dataset.xlsx