Getting Monthly Revenue from a Date Range

[C_Hall]

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

[ShankerV]

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. 

[KSowers · Accepted solution]

Give this workflow a shot! This is how I would do it. Please mark as a solution if this works for you. If not, let me know how we can fix it!

Edited: Added a few tools to help sort. You could do this a few different ways. 

[Felipe_Ribeir0]

Hi @C_Hall 

Please see if this is what you need

INPUT

OUTPUT

[C_Hall]

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.