split time period into rows

[Mic]

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 solution]

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.

[Mic]

Hi @kelly_gilbert

Thanks for your help. It works perfectly.