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
Solved! Go to 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.
Hi @kelly_gilbert
Thanks for your help. It works perfectly.