Calculate dates based on amount of months and create columns

[KittieJames]

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. 

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!

[davidskaife · Accepted solution]

Hi @KittieJames 

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

[binuacs]

@KittieJames one way of doing this

[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.

[davidskaife]

Hi @KittieJames 

Try this - it even accounts for Leap Years!