cancel
Showing results for
Did you mean:
Regular Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

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

Hi @JoeRandI ,

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Community Support Team

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

Hi @JoeRandI ,

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

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

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?

Regular Visitor

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

Hey Cherry,

Any thoughts on how to do that output?

Joseph

Regular Visitor

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

Figured it out, thanks for help.

Announcements