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

Question

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

Service ChargeStart DateEnd DateA£3001/06/202531/08/2025B£4.5025/05/202524/06/2025C£1014/05/202513/06/2025D£9.5001/06/202530/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?

atcodedog05 · Answer

Hi @AnjaBabble ,

Here is my take on it.

Cheers and Happy Analyzing :)

Charge.yxmd

mceleavey · Answer

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.

mceleavey_solution.yxmd

AnjaBabble · Answer

Hi M,

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

Service ChargeStart DateEnd DateMay-25Jun-25Jul-25Aug-25A£3001/06/202531/08/2025 £10£10£10B£4.5025/05/202524/06/2025£0.90£3.60  C£1014/05/202513/06/2025£5.67£4.33  D£9.5001/06/202530/06/2025 £9.5