Need help splitting data with start/end dates into monthly columns

[AnjaBabble]

I have a data set which is set out like the following: 

Service	Charge	Start Date	End Date
A	£30	01/06/2025	31/08/2025
B	£4.50	25/05/2025	24/06/2025
C	£10	14/05/2025	13/06/2025
D	£9.50	01/06/2025	30/06/2025

I want to change the data so rather than having start/end dates, each month is a column with the relevant charge for that period showing in the column. How can I do this? 

[mceleavey]

Hi @AnjaBabble ,

are you saying you want the dates converted to just months? Can you provide a mock up of what you would like to see?

M.

[AnjaBabble]

Hi M, 

Id like to be able to split the charge over the relevant months. So it would look something like this:

Service	Charge	Start Date	End Date	May-25	Jun-25	Jul-25	Aug-25
A	£30	01/06/2025	31/08/2025		£10	£10	£10
B	£4.50	25/05/2025	24/06/2025	£0.90	£3.60
C	£10	14/05/2025	13/06/2025	£5.67	£4.33
D	£9.50	01/06/2025	30/06/2025		£9.5

[mceleavey]

Hi @AnjaBabble ,

I first converted the date fields so I could do datetime calculations, this is so I could get the Generate Rows tool to create the values for each month in between the Start and End dates.

I then simply counted the instances and joined that back to the stream by each Case. Then it was a simple case of converting the charge to a numeric value and dividing it by the count. Then I cross-tabbed it to the required format:

Workflow attached.

I hope this helps,

M.

[atcodedog05]

Hi @AnjaBabble ,

Here is my take on it.

Cheers and Happy Analyzing :)