Pivot many to many relationships

Another variant/option for you, this is a different approach if we're not doing pivoting. Just simply put the given formula in one cell, and it will spill out the results. Make sure that the covered cell of that formula is empty. Since that formula essentially creates a summary table that combines dates, gains, and expenses from two different tables, calculates the totals, and presents the data in a structured format, using LET function.

In the formula itself it contains header for the table, data (date, values), and totals, net gain
here's the revision of the formula for easy editing of ranges.

=LET(
  header,{"Date","Gain","Expense"},
  dateCol,SORT(
    UNIQUE(
      VSTACK(
        FILTER(Table1[date], Table1[est]=L1, ""),
        FILTER(Table2[date], Table2[est]=L1, "")
      )
    )
  ),
  gainCol,IFERROR(XLOOKUP(dateCol,Table1[date],Table1[gain],0),0),
  totalGain,SUM(gainCol),
  expenseCol,IFERROR(XLOOKUP(dateCol,Table2[date],Table2[expense],0),0),
  totalExpense,SUM(expenseCol),
  gainexpense, (totalGain-totalExpense),
  totalrow, HSTACK("",totalGain,totalExpense,gainexpense),
  combinedData,HSTACK(dateCol,gainCol,expenseCol),
  IFERROR(VSTACK(header,combinedData,totalrow),"")
)

What needs to change to your actual table/ranges:

1. EST Ranges:
   • Replace Table1[EST] and Table2[EST] with the actual establishment column names in both tables
2. Column Names:
   • Replace Table1[date] and Table2[date] with the actual date column names in both tables.
   • Replace Table1[gain] with the actual gain column name in the gain table.
   • Replace Table2[expense] with the actual expense column name in the expense table.

Purpose of Each Part inside LET function:

1. Header:

   • Defines the headers for the final table: {"Date","Gain","Expense"}.

2. Date Column (dateCol):

   • Combines and sorts unique dates from both tables where the establishment (est) matches L1.

   • Uses FILTER to get dates from both tables.

   • Uses VSTACK to stack the filtered dates.

   • Uses UNIQUE to remove duplicates.

   • Uses SORT to arrange the dates in ascending order.

3. Gain Column (gainCol):

   • Uses XLOOKUP to find the gain values from the gain table for each date in dateCol.

   • IFERROR ensures that if a date is not found, it returns 0.

4. Total Gain (totalGain):

   • Calculates the sum of all values in gainCol.

5. Expense Column (expenseCol):

   • Uses XLOOKUP to find the expense values from the expense table for each date in dateCol.

   • IFERROR ensures that if a date is not found, it returns 0.

6. Total Expense (totalExpense):

   • Calculates the sum of all values in expenseCol.

7. Net Gain (gainexpense):

   • Calculates the difference between totalGain and totalExpense.

8. Total Row (totalrow):

   • Creates a row with the total gain, total expense, and net gain

9. Combined Data (combinedData):

   • Horizontally stacks dateCol, gainCol, and expenseCol.

10. Final Output:

    • Uses IFERROR to ensure that if there's an error in the final table creation, it returns an empty string.

    • Uses VSTACK to stack the header, combined data, and the total row to create the final table.