Forum Discussion
Subscription revenue based on transaction-level data
Community SupportHi User068765 ,
Please try:
Measure =
var _a = ADDCOLUMNS(ALL('Table'),"commission",[Price]*IF([Transaction Type]="Cancelled",-1,1)*CALCULATE(MAX('Table (2)'[Commission])))
return SUMX(FILTER(_a,[Transaction Date]<=MAX('Date'[Date])),[commission])Final output:
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,
Thank you very much for this - it seems to be getting close to what I am trying to achieve. I have been trying to modify it to account for the fact that there is also expiry date for these transactions but don't seem to be getting anywhere.
| Customer | Age | Transaction Type | Product | Price | Transaction Date | Subscription End Date |
| A | 20 | Purchased | 1 | 30 | 01-Oct-22 | 01-Oct-23 |
| B | 40 | Purchased | 2 | 50 | 15-Oct-22 | 15-Oct-25 |
| A | 20 | Cancelled | 1 | 30 | 08-Oct-22 | 01-Oct-23 |
| C | 60 | Purchased | 1 | 10 | 01-Nov-22 | 01-Nov-24 |
| C | 60 | Cancelled | 1 | 10 | 01-Jan-23 | 01-Nov-24 |
I tried modifying the Date in the SUMX but that doesn't seem to work. Basically, after the end date for each customer, I should not be earning commission anymore. If we were looking at it from the point of 2021, I should also not be earning anything from 2021 to October 2022 since the first transaction start then. Is there any way to incorporate this?
Thanks again for your help!