I'm trying to create one "Statement" table so I can display the monthly and current balance for our clients. We collect royalties for our clients. I have three different tables, each rows have a date, from which each client would has an income or an expense, i.e.
TABLE 1
Date - Statement Period - Song - Artist - .... - Net Payable
TABLE 2
Date - Statement Period - Song - Artist - .... - Net Payable
TABLE 3
Date - Statement Period - Client - Income - Commission - Net Payable
What I'm trying to do is create a new table for Power Bi that woud look like
Date - Statement Period - Table 1_Net Payable - Table 2_Net Payable - Table 3_Net Payable - Balance (Sum of Net Payable)
This way I'd be able to create a report that shows each client their previous month's balance and their current balance with a breakdown of where that comes from, i.e., Table 1_Net Payable + Table 2_Net Payable + Table 3_Net Payable.
I have tried creating a new table with a Calendar Auto function to create a date relationship between the three tables and then add all the columns onto one type of master calendar. But once I made the Master Calendar table, I have not been able to add the columns from the other tables onto that table in the modeling section. I have been able to display the values side by side on the single calendar on the report but I still don't know how to create the measure or columns that will allow me to display a balance, not the previous month's balance or the current balance.
Help!
