Forum Discussion
Subscription revenue based on transaction-level data
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?