Creating an allocation table using dates

[PNW12]

Hi,

Looking for some help around how I can allocate spend out by months if I have a start and end date for each project line.

My Data provides the following information 

Start Date	End Date	Requisition Spend
6/30/2020	7/31/2020	436,937
6/30/2020	7/31/2020	604,544
6/30/2020	7/31/2020	3,000,000
6/30/2020	7/31/2020	271,088
9/8/2020	10/31/2020	15,500,000
12/10/2020	12/15/2020	2,200,000

And I need to show % expense landing in each month and the amount for the next 36 months.

Start Date	End Date	Requisition Spend	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21	Total % Complete
6/30/2020	7/31/2020	436,937	0%	0%	0%	0%	3%	97%	0%	0%	0%	0%	0%	100%
6/30/2020	7/31/2020	604,544	0%	0%	0%	0%	3%	97%	0%	0%	0%	0%	0%	100%
6/30/2020	7/31/2020	3,000,000	0%	0%	0%	0%	3%	97%	0%	0%	0%	0%	0%	100%
6/30/2020	7/31/2020	271,088	0%	0%	0%	0%	3%	97%	0%	0%	0%	0%	0%	100%
9/8/2020	10/31/2020	15,500,000	0%	0%	0%	0%	0%	0%	0%	43%	57%	0%	0%	100%
12/10/2020	12/15/2020	2,200,000	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%	100%	100%

Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21
0	0	0	0	13,654	423,283	0	0	0	0	0
0	0	0	0	18,892	585,652	0	0	0	0	0
0	0	0	0	93,750	2,906,250	0	0	0	0	0
0	0	0	0	8,472	262,617	0	0	0	0	0
0	0	0	0	0	0	0	6,601,852	8,898,148	0	0
0	0	0	0	0	0	0	0	0	0	2,200,000

Thanks for your help!

[Luke_C · Accepted solution]

Hi @PNW12 

Here's an approach that should get you in the right direction. Let me know if you have any questions

[apathetichell]

Does the data source also include a project code and is it broken up by specific month...

IE is 6/30/2020 7/31/2020 $436,937 (project code)

actually the sum of two lines with

6/30/2020 13,654 (project code)

7/30/2020 423,283 (project code)

or with the dates, percentages, project code?

If not - how are the monthly divisions created from the aggregate date range and total project costs?