Contract amount distribution among the current year based on the period

[Akash_Raja]

Hi,

Input data:

contract	start date	end date	Total Amount	Type
C1	Jan 1,2024	Dec 31, 2024	1000	Annually
C2	Mar 1, 2023	Apr 30, 2025	12000	Monthly
C3	Jul 1, 2023	Jun 30, 2024	5000	Quaterly
C4	Jan 24, 2024	Mar 1, 2024	300	Monthly
C5	Mar 3, 2020	Mar 2, 2025	5000	Annually

Output:

	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sept	Oct	Nov	Dec
C1	1000
C2	500	500	500	500	500	500	500	500	500	500	500	500
C3	1250			1250
C4	100	100	100
C5			1000

Actually I need to create a Matrix visualisation like the output shown above.

The main motive of this dashboard is for planning the budget for the current year only

Calculation part-

The amount value should divide based on the schedule. 

Eg. For C2 the total duration of the contract is 24 months then 12000*24 = 500 which is the monthly cost, same like that if it's Annually then the amount should divide once a year based on the total duration of the contract period and If quaterly then number of quaters among the contract period 

Visualisation part-

Basically it should be in Matrix visualisation and it should only Shows the amount split for the current year only but for the calculation of amount can take the whole part of contract period.

Note: this is sample data but my data has some ~1000 line items.

[lbendlin · Accepted solution]

For C2 the total duration of the contract is 24 months then 12000*24 = 500 

Your sample data for C2 lists 25 months, not 24.  C4 only goes over 2 months, not 3.