split time period into rows

Question

Hi There!

I am looking for a way to split the time period into the individual months and based on the days, split the invoice amount.

Example

If my data looks like this:

Start

End

Invoice_Amount

2021-02-05

2021-02-20

50,18

2020-10-30

2021-03-05

129,83

I would like that if a period goes over several months, rows are automatically generated, and the invoice amount is split proportionally to the days.

My output should look like this (the last two columns I can already calculate):

I hope someone can help me

BR

kelly_gilbert · Accepted Answer

Hi, @Mic - this is a great use case for the Generate Rows tool!

Here, we are telling it to start at the [Start] date, then increment by one month until the [End] date is reached:

From there, you can use a Formula tool to calculate the end of date, e.g. min([End], datetimeadd(datetimeadd(datetimetrim([START_period], 'month'), 1, 'month'), -1,'day')),

as well as your number of days in the period.