Forum Discussion
Putting numbers in a table based on start date/end date.
Hey All,
Currently, I have a table that is looking at the contract level (with customers having multiple contracts), the start date of the contract, the end date, and the revenue per year.
So something like this:
CustomerID Annual Value: Revenue Start Date: Revenue End Date: Product:
123: 23,000 2/2/2018 2/2/2019 Car Lease
123: 12,000 2/3/2018 4/4/2019 Car Lease
456: 5,000 2/7/2018 5/8/2018 Boat lease
I've created a calendar that starts at the earliest revenue start date, and ends at the latest revenue end date (looking at each month). I want to populate this table with a sum of the revenue in annual value for each customer per month. So something like:
Feb 2018: Customer 123 - 23k annual value - Car Lease
Feb 2018: Customer 123 - 12k annual value - Car lease
Feb 2018: Customer 456 - 5k annual value - boat lease
Any ideas?
Hi Anonymous ,
I have a little confused about your desired output.
I've created a calendar that starts at the earliest revenue start date, and ends at the latest revenue end date (looking at each month). I want to populate this table with a sum of the revenue in annual value for each customer per month. So something like:
Feb 2018: Customer 123 - 23k annual value - Car Lease
Feb 2018: Customer 123 - 12k annual value - Car lease
Feb 2018: Customer 456 - 5k annual value - boat lease
It seems that you want to calculate the revenue everymonth per customer, so the output should be like below.
Why do you want to have 2 records for customer 123 in Feb 2018?
If it is convenient, please describe your logic in more details so that we could help further on it.
Best Regards,
Cherry
4 Replies
v-piga-msftResident Rockstar
Hi Anonymous ,
I have a little confused about your desired output.
I've created a calendar that starts at the earliest revenue start date, and ends at the latest revenue end date (looking at each month). I want to populate this table with a sum of the revenue in annual value for each customer per month. So something like:
Feb 2018: Customer 123 - 23k annual value - Car Lease
Feb 2018: Customer 123 - 12k annual value - Car lease
Feb 2018: Customer 456 - 5k annual value - boat lease
It seems that you want to calculate the revenue everymonth per customer, so the output should be like below.
Why do you want to have 2 records for customer 123 in Feb 2018?
If it is convenient, please describe your logic in more details so that we could help further on it.
Best Regards,
Cherry
Hi Cherry,
I think you're outcome works.
Ideally, we look at the end day of every month (so EOMONTH in there) and say recurring revenue per year is X. if the next month is 5k lower than X, we know the customer churned revenue in some way. If we filter it by product, we can also see the performance of boat leases vs car leases, in this case.
How do I do that outcome?