Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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.
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êsThis 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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCorrect! 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
Proud to be a Super User!
Check out my blog: Power BI em Portuguêsso 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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
90 | |
84 | |
70 | |
49 |
User | Count |
---|---|
141 | |
121 | |
112 | |
59 | |
58 |