I have two tables in power query, one has a list orders with bonus calculation for each orders (bonus is calculated in Power Query), and the second table has list of monthly payments to each sales person. Monthly payment is generally a sum of bonuses taken for a certain date. Is it added to the table manually every month for every person.

The list of orders is subject to constant corrections, so the amount of bonus may change a little after it has been paid and the balance must be included into the following month.

The table with orders has in fact many columns that influence the bonus calculation. I haven’t included them into the example.

In order to use bonus and fact together in measures, I’ve merged the tables based on names and months, so every row of the table Orders now has the amount of payment of the respective month.

My skills in Power BI are basic. I have made visualization as a matrix with name, monthly bonus, monthly payment and balance for each month calculated as:

Total monthly bonus = SUM(‘Orders'[bonus])
Balance = ‘Orders'[Total monthly bonus] – MIN(‘Orders'[bonus paid])

But what I need is the cumulative balance (in order to find out how much we should pay this month considering past corrections).

It took me some days to manage cumulative totals of bonuses and payments filtered by names and months, but they belong to different tables and I can’t deduct one from another.

What I actually need is to calculate what will the bonus be this month for each name considering past corrections.

Any ideas and recommendations on general approach how to calculate it will be appreciated.