Calculate dates based on amount of months and create columns

Hello,

I am trying to create date columns based on the 'billing from' month and the 'billing to' month and how many months are being billed.

For example on row 1, billing is from 01/12/2024 to 31/01/2025 and so you can see this is for 2 months and the 'Revenue' column is the amount that needs to show as being billed in each month.

Alteryx query.png

So ideally for row 1 what I would see is highlighted in yellow which is a column labelled 01/12/2024 with 22.31 under it and another column labelled 01/01/2025 which also has 22.31 under it.

Row 2 you can see billing is from 01/12/2024 to 28/02/2025 so 3 months are now being billed and again I need columns 01/12/2024, 01/01/2025 and 01/02/2025 each with '9' under it.

Thanks for help in advance!

Alteryx query.xlsx

Tips and Tricks

Date Time

Accepted answers

davidskaife

Hi @KittieJames

Here you go, using RecordID Generate Rows, and Cross Tab tools:

Billing.yxmd

All comments

davidskaife

Hi @KittieJames

Here you go, using RecordID Generate Rows, and Cross Tab tools:

Billing.yxmd

binu_acs

@KittieJames one way of doing this

Calculate_dynamically_13022025.yxmd

KittieJames

Thanks All, that worked perfectly!

KittieJames

Thank you, this worked perfectly! I have something a bit more complex and not sure if its possible but on the below data I am trying to do the same thing and create month columns based on the 'billing from' month and the 'billing to' month but this time the amount to be recognised in each month needs to be calculated as they are not full months.

'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

Alteryx query 2.xlsx

davidskaife

Hi @KittieJames

Try this - it even accounts for Leap Years!

Billing v2.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703