We have a list of service agreements with start and end dates and agreed monthly service fees. I am trying to track the sum of those monthly service fees by month. What is the best way to do this?
Example:
Agreement 1 starts 01-01-2022 and ends 12-31-2022 and runs $100/mo.
Agreement 2 starts 07-01-2022 and ends 06-30-2023 and runs $50/mo.
I want an output for the total monthly fees from each agreement in any given month.
Jan 22: 100
Feb 22: 100
Mar 22: 100
Apr 22: 100
May 22: 100
Jun 22: 100
Jul 22: 150
Aug 22: 150
Sep 22: 150
Oct 22: 150
Nov 22: 150
Dec 22: 150
Jan 23: 50
Feb 23: 50
Mar 23: 50
Apr 23: 50
May 23: 50
Jun 23: 50
etc
¡Resuelto! Ir a solución.
Hi @C_Hall
It can be achieved with the help of Summarize tool to get the max and min dates.
Then use generate rows tool to generate each month.
Add the values to get the result.
Let me know if you need me to create a workflow for the same.
All of these contained a seed to a solution, but I can only mark one, so I marked the closest one. The best resolve was to use DateFromParts on the date, either via formula tool or via SQL in the original data input source, and then the SUM tool. Thank you all for your help! Coming in clutch as always.