Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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. 

Impactful Individual
Impactful Individual

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

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors