Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Not applicable

Complex calculations to predict number of paying customers

I spent two days on this, ending up with too complex models that file slowed down to a point of being unusable. So I hope someone here can point me towards simpler solutions. 

 I have user data like this (example): 

12301/01/2018 15/01/20180
45601/01/201801/06/2018 1


And I receive predictions from a collegue for number of sign ups per month next year. They include both new customers with trial and returning customers who will directly become paying.


Month Next Yearsignup


Based on these, I want to forecast number of paying customers per month next year. The formula would be in two parts:

Some definitions:

returning%: percentage of users that with returning = 1

conversion%: percentage of new users who will become paying after the trial (trial_end but no end date)

signup_previous : number of signups previous month 


here i will need to calculate conversion% and returning% based on the first data table, based on historical data.


So that'll either be a table: month, conversion%  and returning%
or a measure that can dynamically calculate the relevant % based on the month to be forecasted, by using the percentages for the same month in the previous year.


new customers (per month):


+ signup(1-returning%)*0.5*conversion% //half of the users to be converted in that month becomes paying that month

+signup_previous*(1-returning%)*0.5*conversion% //other half becomes paying customers next month, because of trial


retention: the number of paying users to be retained from the previous month, based on their customer age (in months), each age  has a retention percentage, based on the historical data. So it would give, per age, % of the customers were retained to the next month. This is the most tricky part for me. I created a calculated table that looks like below, but that slowed down the file, so I'm looking for alternative solutions:

Per month, per age, number of paying customers, created using the first table with user_id and dates:

Year MonthAge (months)Number of paying customers 


Forecasted paying customers = new customers + retention 

Forecasted paying customers would be a measure that gives the total of forecast per month next year.


The end table would look like this (sample), and users then can export it to use. 


Month Next YearForecasted paying customers
January 20212000
February 20211000


I know it's complex, but I appreciate any direction, tips etc. I'm not expecting someone to come up with a full solution. 

Community Support
Community Support

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.


Best Regards,

Rico Zhou

Community Support
Community Support

Hi @Anonymous 

Do you want to calculate "forecasted paying customer" by previous years data?

From your statement, I think your calculate logic is as below.

Firstly you have a Data table with user data in it.

I think you may want to calculate "forecasted paying customer" in 2021 by Data in 2020. Is it right?

You will calculate returning% and conversion% by month in 2020 by this data table.

EX. 2020 Jan ,

Here I have a problem, shall we separate the users by Start month?

Ex: UserA Start date = 2020/1/31, so he will be assign to January.

Measure should looks like:

countx(Filter(all(Data Table), Data Table[StartMonth] = Max(Data Table[StartMonth]))),Data Table[User])

I am confused about your conversion% .

In your sample 456, start in 2020/01/01, but his end data in 2018/06/01.

Should we distributed assign him to January?

You can show me more details about your calculate logic and the result you want.

And how can we calculate retention ? Is it "Number of paying customers" in an other table?

Your calculate is complex, you can share a sample to me by your OneDrive for Business and show your the result you want.

This may make it easier for me to understand.


Best Regards,

Rico Zhou


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors