Projection of 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 recurring monthly commission earned by me. I need to do a projection for future periods based on current profile and using some sort of probability based on the profile of the customer. 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
...	...	...	...	...	...

The probability of someone cancelling is based on their age: 

Age	Probability of cancelling
20	0.5
21	0.4
22	0.2
...	...
100	1.0

For the projection of future commission, I will need to multiply the probability with the price paid in table 1. 

My question here is: 

Given only one single line entry data, how do I get customer A (as an example) to multiply by age 21 when he reaches that age in the next year? I would like to do this then aggregate the projected commission for future years for all customers.  

If it might help, I posted a prior question relating to this data analytic problem I have for further context: 

Subscription revenue based on transaction-level da... - Microsoft Power BI Community

Thank you in advance. 

[Tanushree_Kapse]

Hi @User068765 ,

You can create these two measures to get the difference from last years date and current date , and the second one to get the current age:

Please find below an example:

1. Diff = DATEDIFF(MAX('Table (2)'[Transaction Date]),TODAY(),YEAR)

2. Current Age = sum('Table (2)'[Age])+[Diff]

3. Updated_price = if([Current Age]=20, sum('Table (2)'[Price])*0.5, if([Current Age]=21, sum('Table (2)'[Price])*0.4, if([Current Age]=22, sum('Table (2)'[Price])*0.2,sum('Table (2)'[Price]))))

Mark this as a solution, if I answered your question. Kudos are always appreciated.

Thanks

[User068765]

Hi @tanushree2091 

Apologies for the lack of clarity, but I would need to do the same for all the customers (actual data about 600 entries) and the age/probability of cancelling table will be for all ages and not just that small snippet. Is there a way to do this that would not take up so much memory/RAM and time? 

Thank you for your help! 

[Tanushree_Kapse]

Hi @User068765 ,

This would work for large entry data too.
Let me know where exactly you are facing the problem after trying this.