Automate billing spread with P/Q

[BSwan]

If I have 3 columns - column A is the qty of contracts, column B holds the start date of the contracts, and column C holds the last Bill date of the contracts. Assume all contracts Bill once a month and for the same amount.

Example: Row 1 : column A: qty 30 contracts. Column B: the contracts all start 1/1/17. Column C: the contracts all end 12/31/20.

Row 2: Column A: qty 25 contracts. Column B contracts all start 2/1/17. Column C the contracts all end 1/31/21.

Etc...

Is there a way to use PQ to automate creating a spread of total contract revenue by month (spread between earliest possible Bill start month and latest possible Bill start month of all rows)?

 

[Ken Puls]

So the answer is that yes, we can, but it's a bit tricky. I cooked up a solution using a custom function to which you pass the qty, start date and end date for each row, and it will calculate the apportioned table with month end dates and quantities. The odd thing though, is that you're contracts won't be split evenly. If you look at the 30 contracts, that works out to 0.625 per month.

Example file is attached.

 

[BSwan]

Ken,
Thank you for the quick response!

I think there is some confusion as to what my original post was referring to.

The qty of contracts are not spread across the start & end dates. They all start billing in the month of column B, and they all bill through the month in column C.

Utilizing the expanding capabilities of the table, the goal is to be able to enter the contract quantity starts by month -- as many rows as is needed depending on the span of months that certain waves of contracts start in. Then, using the static recurring monthly revenue (RMR) amount per contract, create a billing spread output, so that cash flows over time can be easily visualized.

I've attached a very simple example of what this would ideally look like.

Really appreciate the advice!

-Blake

 

[Ken Puls]

You mean like this?

 

[BSwan]

Perfect! Thanks, exactly what was needed.

Looks like the function is doing most of the heavy lifting. I purchased your book awhile back -- do you reference anything similar to this in that book?
Just looking to try and get a better understanding of the logic behind building the function (outside of checking out the steps & Code in the advanced editor in the sample above).

 

[Ken Puls]

We don't cover that exact thing in the book, no, but the concepts are all there between Chapter 19-21.

To see how it works, duplicate the function (and rename it). You can then edit the copy, comment the very first line, (put // in front of it) and un-comment the two lines after the let statement (remove their //). That will bring the applied steps back so that you can see how I built it.

Let me know if you need any help deciphering it.

 

[BSwan]

Thanks for the advice on getting to see the steps to make the function.

I noticed in the beginning of creating the function, it looks like you manually used 10/1/17 as the dateStart & 1/31/18 as the dateEnd (which matches the original dataset provided).

All the remaining steps make sense -- we are listing out the full month end dates inbetween. Then the function is applied to each row back in the Cashflow Query, and then finally all the revenue amounts are grouped (summed by month).

My question -- how does the function know to use the dates then from the real data in dynamic function? How does it know to replace the originally mannually entered start & end dates with the current data upon refresh? I changed the dates completely in the originally data table, and the output cashflow table handled it cleanly.

Thanks!

-Blake

 

[Ken Puls]

Tell you what... read through the first few pages of Chapter 21. That will explain the very first line of the function, and how the items get passed into it.

 

[BSwan]

Ken, thanks for the tip, that chapter helped.

One item I cannot seem to figure out -- when you write a blank query from scratch, the first step is always "Source". I noticed on your function you were able to have the first step be dateStart, followed by dateEnd. Then the source step came in. I've tried to replicate but I cannot seem to figure out how? When I tried to replicate, the "Source" step cannot be renamed, or moved?

Thanks,

-Blake

 

[Ken Puls]

I went into the advanced editor and just typed my new variables before the source step. They weren't recorded (a recorded query always starts with Source, although you can change it in the M code if you prefer.)

 

[BSwan]

Figured that was the case. Thanks again for the advice on this one!