Putting numbers in a table based on start date/end date.

[Anonymous]

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?

[v-piga-msft · Accepted solution]

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

[Anonymous]

Figured it out, thanks for help.

[Anonymous]

Hey Cherry,

Any thoughts on how to do that output?

Joseph

[Anonymous]

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?