cancel
Showing results for
Did you mean: Frequent Visitor

## Projection of revenue based on transaction-level data

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

3 REPLIES 3  Super User

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:

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 Frequent Visitor

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?  Super User

Hi @User068765 ,

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