I have a table of entities (companies) that transact with each other. I have a table of transactions between these entities. I would like to create a pivot table in Excel 2013 that summarizes the cash flow of the transactions for each entity and for each date.
The project xlsx file is attached. The sheets are the following:
Output required: what the result should look like
Attempt_1: what I tried with a measure
Attempt_2: what I tried with a calculated field
Entities: the source table of entities
Transactions_1: the transactions table (used for borrower)
Transactions_2: an exact copy of the transactions table to provide the (used for lender)
Please use only Excel, Power Pivot and DAX, no VB. The simpler the solution the better.
To be clear, the result needed is simply the "cash flow", which is the sum of positive [amount] if you are a lender and the negative [amount] if you are a borrower for any transaction