cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JoeRandI Regular Visitor
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
Community Support Team

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

Hi @JoeRandI ,

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.

Capture.PNG

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.

View solution in original post

4 REPLIES 4
Community Support Team
Community Support Team

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

Hi @JoeRandI ,

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.

Capture.PNG

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.

View solution in original post

JoeRandI Regular Visitor
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? 

JoeRandI Regular Visitor
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

JoeRandI Regular Visitor
Regular Visitor

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

Figured it out, thanks for help.

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors