cancel
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:

 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.

3 REPLIES 3
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.

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?

Thank you for your help!

Impactful Individual

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

Announcements

#### Power BI Monthly Update - November 2023

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

#### Fabric Community News unified experience

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

#### 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