Revenue by new customers in a year by month

[fazza1991]

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.

[MFelix]

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

[fazza1991]

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

[MFelix]

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?

[fazza1991]

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

[MFelix]

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?

[fazza1991]

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

and yes please monthly