Forum Discussion
Subscription revenue based on transaction-level data
Community SupportHi User068765 ,
Please explain in detail why there is no Customer A's commission in the November commission? Is the calculation of commission based on the comments? Or is there some other calculation logic?
Sorry for that the information you have provided is not making the problem clear to me. Can you please share more details to help us clarify your scenario? Please provide me with more details about your table and your problem.
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi v-jianboli-msft,
Apologies for the lack of clarity. Customer A will not be considered in November because he cancelled on 31-October-2022. You have caught the crucial point to the difficulty I am facing - I am assuming that all entries will keep earning commission for the month unless I am told otherwise. How do I take into account the fact that I will not be including commission for customer A in the later months? One thing I can think of is to have a sort of flag where purchased will be 1, and cancelled will be -1, which will cancel the subsequent months.
I realise the dates may be causing confusion, so let me add a couple of other scenarios and amend the above scenario.
| Customer | Age | Transaction Type | Product | Price | Transaction Date | Oct-22 | Nov-22 | Dec-22 | Jan-23 | Comments |
| A | 20 | Purchased | 1 | 30 | 01-Oct-22 | = 0.01 * 30 | None earned for October as customer cancelled in subsequent transaction | |||
| B | 40 | Purchased | 2 | 50 | 15-Oct-22 | = 0.005 * 50 | = 0.005 * 50 | = 0.005 * 50 | = 0.005 * 50 | Earned until current date (whenever this is) as no subsequent cancellation |
| A | 20 | Cancelled | 1 | 30 | 08-Oct-22 | =- 0.01 * 30 | See Customer A above | |||
| C | 60 | Purchased | 1 | 10 | 01-Nov-22 | = 0.01 * 10 | = 0.01 * 10 | = 0.01 * 10 | Earned from Nov to Dec, but Jan cancelled by subsequent transaction | |
| C | 60 | Cancelled | 1 | 10 | 01-Jan-23 | =- 0.01 * 10 | See above |
The way I can think of of taking into account the later entries of data is by adding a flag of sorts (+1/-1) so that the items cancel out like below. But I am not sure how to implement this by calculating commission for subsequent months based on one line entry? Do I use a cross table?
| Customer | Age | Transaction Type | Product | Price | Transaction Date | Oct-22 | Nov-22 | Dec-22 | Jan-23 |
| A | 20 | Purchased | 1 | 30 | 01-Oct-22 | = 0.01 * 30 | = 0.01 * 30 | = 0.01 * 30 | = 0.01 * 30 |
| B | 40 | Purchased | 2 | 50 | 15-Oct-22 | = 0.005 * 50 | = 0.005 * 50 | = 0.005 * 50 | = 0.005 * 50 |
| A | 20 | Cancelled | 1 | 30 | 08-Oct-22 | =- 0.01 * 30 | =- 0.01 * 30 | =- 0.01 * 30 | =- 0.01 * 30 |
| C | 60 | Purchased | 1 | 10 | 01-Nov-22 | = 0.01 * 10 | = 0.01 * 10 | = 0.01 * 10 | |
| C | 60 | Cancelled | 1 | 10 | 01-Jan-23 | =- 0.01 * 10 |
Final commission earned should be as follows:
| Oct-22 | Nov-22 | Dec-22 | Jan-23 |
| 0.25 | 0.35 | 0.35 | 0.25 |
Thank you very much for your help! And do let me know if there's anything I can do to make it clearer.
Hello @v-jianboli-msft,
I have gotten to a place where it is quite close to what I would like to achieve based on mede's answer in this thread. However, I faced a similar issue as the original poster where the subtotal does not seem to show a sum for the year but is instead the exact figure calculated. I understand that the logic is probably not exactly correct hence this issue. Is there a way to achieve a similar result but with the correct subtotal?