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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
fazza1991
Helper II
Helper II

Revenue by new customers in a year by month

Hi,

 

I am trying to calculate new customer revenue by month for a 12 months period beginning their 'LIVE DATE'

 

I have a Calendar Table but cannot create active relationship to the live date due to other data relationships.

 

See below example of what i am trying to achieve.

 

I have total revenue each month - but how much of that is generated by clients that started in that year.

 

 Jan-21Feb-21Mar-21Apr-21
Total Revenue1000200010005000
New Client Revenue2005006001500

 

I have tried many different calucaltion such as use relationships etc but these only give me the revenue for the month they went live rather than how much revenue generated per month for a period of 12 months from their live date.

 

 

6 REPLIES 6
MFelix
Super User
Super User

Hi @fazza1991 ,

 

You can create an inactive relationship and then using the USERELATIONSHIP you can make the calculation would be something similar to this:

 

New Cliente Revenue = CALCULATE( SUM(Table[Revenue]), USERELATIONSHIP(Table[Live Date], DateTable[Date]))

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



This does not work i am afraid, sum up the revenue on the date client went live rather than the revenue made each month for the next 12 months

Hi @fazza1991,

 

Can you please tell me if I'm understanding correctly what I refer below

 

You want on each month to calculate the total sales of the next 12 months of the customers that went live that month

 

So for example if customer A is live on January 2021 and have sells of 10.000 until today you want to show in January the 10.000? Or do you want to consider only the part of sales for that customer from January 2021 until January 2022 so 1000 in January 2021, 1000 in February 2021 and so on until 2022?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Correct! From Jan21 - Dec21 or if a client started half-way through the year it would be Jul21-Dec21.

 

i have a partial idea to create a new column and do ENDOFYEAR(LiveDate,"31/12") but for some reason some of them dont always go to the last day of the year some random date but only handful

Hi  @fazza1991 ,

 

You just lost me, you just want the total until December not until july next year?

And once again you want it to be monthly value right?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



so the best way to explain is - i would like the in year revenue

 

so example of from and to dates when a client goes live

 

ClientClient LiveRevenue fromRevenue to
A01/01/202101/01/202131/12/2021
B19/03/202119/03/202131/12/2021
C18/07/202118/07/202131/12/2021
D17/10/202117/10/202131/12/2021
E15/12/202115/12/202131/12/2021

 

and yes please monthly

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.