How to calculate the difference between 2 columns in 2 separate tables in Power BI

Question

I have the following 2 tables with expenses and incomes by month:

income table

expense table

I'm having a hard time calculating the information income - expense (profit) for each month,

The result should be another table like this:

result table

What is the best way to accomplish this ?

For context, the tables of the datasource are not grouped by month and year, these are only the grouped by sum, these tables have a many to many relationship

Answer 1

You need a Date/Calendar table that will act as a Dimension to your two "fact" tables. I'm assuming you have a date column in both of these two tables.

You can create a Date/Calendar table by creating a Calculated Table with the following:

DimDate = 
  ADDCOLUMNS(
    CALENDARAUTO(),
    "Year", FORMAT([Date], "yyyy"),
    "Month", FORMAT([Date], "MMMM"),
    "Month num", MONTH([Date])
  )

Once created, click on the Month and do a Sort by column on Month num.

Then create relationships from this new Date table to your two table (One-to-Many, single direction), looking like:

Once you have this, the rest is easy. Create the following measures:

$ Income = SUM('Income'[income])

$ Expense = SUM('Expense'[expense])

$ Profit = [$ Income] - [$ Expense]

That's it. Ensure you use the columns from the Date table in visuals with these measures (and not the dates from the "fact" tables).