cancel
Showing results 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

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-21 Feb-21 Mar-21 Apr-21 Total Revenue 1000 2000 1000 5000 New Client Revenue 200 500 600 1500

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

Proud to be a Super User!

Check out my blog: Power BI em Português

Helper II

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

Super User

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

Proud to be a Super User!

Check out my blog: Power BI em Português

Helper II

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

Super User

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

Proud to be a Super User!

Check out my blog: Power BI em Português

Helper II

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

 Client Client Live Revenue from Revenue to A 01/01/2021 01/01/2021 31/12/2021 B 19/03/2021 19/03/2021 31/12/2021 C 18/07/2021 18/07/2021 31/12/2021 D 17/10/2021 17/10/2021 31/12/2021 E 15/12/2021 15/12/2021 31/12/2021

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors