Hi All,
I have the below table.
| Start Date of Contract | End Date of Contract | Operation Name | Comprehensive O&M Price |
| 1/7/2019 | 31/8/2019 | X | 375 |
| 1/9/2019 | 31/3/2020 | X | 405 |
| 12/5/2020 | 31/12/2021 | X | 385 |
| 1/4/2017 | 31/3/2018 | Y | 380 |
| 1/4/2018 | 31/3/2020 | Y | 410 |
| 11/5/2020 | 31/12/2021 | Y | 303 |
Based on the start date of contract and end date of contract for each Operation Name, I need to calculate the annual contract for each operation name.
Expected Output with Calculation:
| Operation Name | Year | Annual Contract | Calculation |
| X | 2019 | 395 | (375*60/180)+(405*120/180) |
| X | 2020 | 353.01 | (405*90/365)+(385*240/365) |
| X | 2021 | 385 | (385*365/365) |
| Y | 2017 | 380 | (380*365/365) |
| Y | 2018 | 396.97 | (380*90/365)+(410*270/365) |
| Y | 2019 | 410 | (410*365/365) |
| Y | 2020 | 300.23 | (410*90/365)*(303*240/365) |
| Y | 2021 | 303 | 303*365/365 |
Please find my explanation for the calculation.
(375*60/180)+(405*120/180)
Here 60 is the number of days for which the contract is valid (end date - start date) and 180 is the total number of days between 1 July 2019 and 31st December 2019. I am using the days approximately but ideally it should be the exact number of days.
For this one: (405*90/365)+(385*240/365)
90 because the contract is from 1st Jan 2020 to 31 March 2020 and 240 is because the the start date is 12/5/2020 and ends at 31st Dec 2020. I am taking rough numbers. Not the exact difference in days.
For the last contract, the contract applies for whole year. Hence 385*365/365
Thanks in advance for the help.
@parry2k @mahoney19 @parry2k @az38 @jdbuchanan71 @mahoneypat @edhans @harshnathani @v-kellya-msft @MFelix @Ashish_Mathur @BA_Pete @ryan_mayu @kbuckvol @Alexander76877 @Petazo @Mariusz @TomMartens @Greg_Deckler @tjd @Sean @mikstra @AllisonKennedy @EricHulshof @briandpeterson @USG_Phil @vpatel55 @mwegener @v-piga-msft @tex628 @sturlaws @Vvelarde @CheenuSing @MarcelBeug @Zubair_Muhammad @v-piga-msft @danextian @MattAL @MattAllington @roalexan @Alexander76877 @kgc
