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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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