Showing results for 
Search instead 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: 


CustomerAgeTransaction TypeProductPriceTransaction Date


The probability of someone cancelling is based on their age: 


AgeProbability of cancelling


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. 

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

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.




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! 

Hi @User068765 ,


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

Helpful resources

T-Shirt Design Challenge 2023

Power BI T-Shirt Design Challenge 2023

Submit your creative T-shirt design ideas starting March 7 through March 21, 2023.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

March Events 2023A

March 2023 Events

Find out more about the online and in person events happening in March!