Table 1: Membership, table 2: Transactions, table3: payments
| Table 1 | |
| Member ID | Alphanumeric |
| Membership Start Date | date |
| Membership End Date | date |
| Transaction Date | |
| Premium Amount | double |
| Fee type ID | short text (i.e. New, Adjustment, Cancel) |
| Table 2 | |
| Member ID | Alphanumeric |
| Membership Start Date | date |
| Transaction Details | text (this is a summary of various columns to keep the example simple) |
| Transaction ID | Alphanumeric |
| Table 3 | |
| Transaction ID | Alphanumeric |
| Transaction Date | date |
| Transaction Total Spend | double |
| Transaction Total Outstanding | double |
In table1 and table3 i calculate the transaction development month relative to a fixed date (the same in both tables) and call this report_month ( TableX[transaction date].month+((TableX[transaction date].year-YYYY)*12) )
Table 1 and table 2 are linked by the Member ID, and have a Many - to - Many relationship as there can be duplicate Member ID's in table 1 (due the possiblity to amend membership or cancel membership)
Table 2 and table 3 are linked by the Transaction ID and have a 1 - to - Many relationship as each transaction ID exists only once in table 2, but multiple times in table 3.
In table 1, you can have multiple transactions per membership (for example New, change of membership level, Cancellation)
Each Row shows the amount paid (or refunded) and so needs to be aggregated at each point
for example a time line of the transactions make look like this if i were to summarise it in a table in power BI
lets assume they make a chane to their membership in month 3 and cancel in month 6 and get a partial refund
note all the tables below are produced using the matrix functionality
| Member | report month 1 | report month 2 | report month 3 | report month 4 | report month 5 | report month 6 |
| 1 | 500 | 20 | 0 | 0 | 0 | -260 |
So the running total may look like this (Achieved in power BI using a measure)
| Member | report month 1 | report month 2 | report month 3 | report month 4 | report month 5 | report month 6 |
| 1 | 500 | 520 | 520 | 520 | 520 | 260 |
In table 2, you have transactions, but this table tracks the running total already rather than the individual transactions so the table would look like this for the total spend (note i dont need the interim step like i did on table 1 to get the running total; i could calculate the incremental point but this is not what i need).
| Member start date | report month 1 | report month 2 | report month 3 | report month 4 | report month 5 | report month 6 |
| Jan | 0 | 0 | 100 | 120 | 140 | 200 |
Transaction Total Outstanding would look something like this
| Member start date | report month 1 | report month 2 | report month 3 | report month 4 | report month 5 | report month 6 |
| Jan | 0 | 0 | 100 | 80 | 60 | 0 |
What im trying to do is create a new table that shows the Transaction Total Spend (from table 3) and the running total membership fee paid (from table 1) in the same table, something like this:
| Member start date | month 1 | month 1 | month 2 | month 2 | month 3 | month 3 | etc... |
| Jan | Runing Total Fee | Total Spend | Runing Total Fee | Total Spend | Runing Total Fee | Total Spend | |
| 1 | 500 | 0 | 520 | 0 | 520 | 100 | |
I've tried to do this but i think there is an issue with the link between the calculated report_month column in table1 and table3, as when i try to do this overall for all memebers, i get a different progression for the Running total Fee if i use the table3.report_month as the column and conversely simmilarly i get a different progression for the Total Spend if i use the table1.report_month as the column.
Ultimately i'd also like to create a table that shows the Total Spend relative to the Runing Total Fee at each month, but coul figure this out so though figuring out how to display the two correctly side by side would be a good start to understand what is goin on.
