I am currently working on a project to calculate losses for certain contracts, and I could really use your help with some challenges I’m facing.

I have successfully calculated losses for each individual contract. The Start Date indicates when the contract should have begun, and the Creation Date is when the payment was received and when the contract officially began.

The issue I’m encountering is related to calculating the total financial delays per year. Some contracts have a Start Date in 2023 but a Creation Date in 2024. My goal is to allocate the total amount of the payments delayed to the appropriate year and month so that I can calculate the number accurately for both 2023 and 2024. If a contract separates between the two years, then the program should calculate how much from that contract was lost in 2023 and how much in 2024 in each month consequently.

I’ve filtered the data to only include contracts starting from January 1st, 2023. My table includes the following columns:

Delay_Days: Calculates the total number of days delayed.

Excess_Delay: Calculates the number of days beyond the first 31 days (since delays within 31 days are not counted for losses).

I’ve been struggling with this for the past three days and feel like I’m missing something. Could you please advise on the best R code to achieve this?

Additionally, any suggestions or advice for improving the report would be greatly appreciated. If there are any aspects you think could be improved, added, or analyzed from a data science or finance perspective, I would love to hear your thoughts.

Thank you so much for your time! I’ve also attached a snapshot of my table for reference (in reality there are 190 contracts that have delay for more than 31 days):