Subscription revenue based on transaction-level data

[User068765]

Hello all, 

I have some transaction data that only gets entered where there is activity from the client, with commission earned by me. I need to calculate the current commission based on this data. To illustrate the solution needed, here is some dummy data: 

Customer	Age	Transaction Type	Product	Price	Transaction Date
A	20	Purchased	1	30	01-Oct-22
B	40	Purchased	2	50	31-Oct-22
A	20	Cancelled	1	30	31-Oct-22
C	60	Purchased	1	10	01-Nov-22

Just to calculate the commission, I will need to multiply the price by a certain percentage based on the product table: 

Product	Commission
1	1.00%
2	0.50%

And the result I get should be as follows (comment to illustrate what is going on): 

Month	Commission earned	Comment
Oct-22	0.55	Customer A & B
Nov-22	0.35	Customer B & C

So my question here is:
1. How do I get the brokerage earned in October to show up in November (and other future months/quarters/years) as well? To incorporate the use of different levels of granularity splicing (i.e. months, quarters, years).  

2. How can I take into account the second customer A transaction where he cancelled it and does not show up in November? An idea I have is to include a commission flag column where the "purchased" show up as 1, and "cancelled" show up as -1 so it will cancel out in the future months (linked to question 1 above) when multiplied with the price. Does this make sense? And how could I implement it? 

It would be good if this could all be geared towards Power Pivot - I understand Power Pivot and Power BI can be slightly different. 

Thank you in advance. 

PS: There's another layer of complexity to this that I will post as a separate question if you could help with as well. 

[v-jianboli-msft]

Hi @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])&&[Subscription End Date]>MAX('Date'[Date])),[commission])

If this couldn't work, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

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.

[v-jianboli-msft]

Hi @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.

[User068765]

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! 

[v-jianboli-msft]

Hi @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.

[User068765]

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. 

[User068765]

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?