Create month columns and calculate values for each month based on total value

Hello,

I have the below data and am trying to create columns based on the 'billing from' month and the 'billing to' month.

'TOTAL UNIT SELLING COST' is what needs to be recognised in each FULL month and the difference between SELL price goes to the 'pro-rata' month.

For example below we have billed 110.56 relating to 10/12/2024 - 28/02/2025. 40.80 needs to be recognised in each full month, i.e Jan and Feb and the difference of 28.96 needs to show in the pro-rata month, in this case December. The difference being the sell price of 110.56 -(2 * 40.80).

On the second row we have billed 71.49 and 64.98 is what needs to be recognised in a FULL month, in this case Feb. The remaining £6.51 needs to be split between Jan and March based on the amount of days being billed.

So ideally I am trying to get what is shown in yellow.

Alteryx query 2.png

Tips and Tricks

Date Time

Accepted answers

davidskaife

Hi @KittieJames

Cross posting here, try the attached (it even accounts for Leap Years!)

Billing v2.yxmd

All comments

OTrieger

@KittieJames
As you will need to day count, for the Billing FROM find the last day of the month and then get the number of days. If equal to number of days in a month, then you have a full month if not, then you will need the # of days for the calculation later.

Very similar do with Billing TO only in this case find the First day of the month and get the total days, if equal to day in month then you have a full month if not then you have the number of days. From these 2 dates you could figure if there is a full month in between. There are several ways how to do it, by creating a full calendar or by getting the month name, etc.
For any full month just assign the monthly full amounts. You can Transpose the data and Sum up the value for all full months. Append that value and with formula tool deduct it from the total. For the first month and last month assign the following value (# of Count days/total days of partial months), Multiple the value that you got after the deduction with the value that you have in the partial months. Now you have the amount that need to be paid in each month.