Calculating Pending Completion Months for an Ongoing Project

Scenario:

The organization must maintain track of all ongoing projects and the number of months remaining to complete them based on a financial year computation. This number of months is meant to be monitored for the duration of the project, beginning with the current financial year and continuing through each subsequent one.

Requirement:

Here, it is necessary that we have a list of every project with the start and end dates mentioned. The number of months left in every financial year until the project is finished must be determined using this information along with the present date.

Assumption: 

In our example we will consider current date as January 15, 2024.

Solution:

We will have project start and end date for each project and along with this we will require all the start and end date of all financial year.

Along with this we will require the current date as we have to calculate the number of months left for each project starting from the current date till its ending date.

1. Project Pending Month : This is the list of all the projects with its start and end date.

2. Financial Year : This is the list start date and end date for each financial year.

So based on the data above for a project named 101 which is starting from May 1, 2023 and is expected to be finished by June 15, 2026, will require an additional 30 months to be completed in total. Of those 30 months, 3 are from the current financial year, 2023–2024; 12 are from the next financial year, 2024–2025; 12 are from the financial year that follows, 2025–2026; and 3 are from the financial year 2026–2027.

The following are the logical steps to calculate the same.

Step 1 : Get the financial year date.

For getting the financial year of the project we will first make a copy of End date column and add 1 year to the end date.

In our case, for project 101 we have end date as 15^th June, 2026, so the financial end date for this project will be 15^th June, 2027.
Hence we will get the financial year for project 101 as 2026-2027.

Step 2: Get the current date column

The current date will help us to know the number of months which we have already spent on the project.

As our requirement is to calculate the number of months which are remaining from the current date.

With the help of today() function we will get the current date.

Step 3: Now join Financial Year start and end date to Project start and end date using Spark SQL

With the help of below query we will get the financial month start date and end date falling between every project start date and end date.

In our case, the start and end date for project 101 is 01-May-2023 and 15-June-2026 respectively, Hence between these dates we have 4 financial years i.e. 2023-2024, 2024-2025, 2025-2026, 2026-2027.
As per our requirement we will need a row for each of these financial year to calculate number of months remaining in each financial year.

Query - SELECT A.*,B.* FROM Project Pending Month A 

              LEFT JOIN Financial Year B ON A.CurrentDate<=B.EndYear AND   

A.FYEndDate>=B.EndYear

Step 4:  Get the Final Start date for each project.

We will compare current date with the financial year Start date, as we need to calculate the number of months remaining starting from the current date.

In our scenario, for project 101 the project start date is 15^th June, 2023 and the current date is 15^th January, 2024 so in this scenario we’ll calculate the number of months from 15^th January, 2024 to 31^st March, 2024 for the financial year 2023-2024.
Similarly, it will calculate for each Financial year falling between the project start date and end date for all the projects.

Hence with the help of below expression we will get our final start date of each financial year for every project.

Expression -  ifCase( dateDiff( "m", FYStartYearDate, CurrentDate )<0, CurrentDate,    

                                               FYStartYearDate) 

Step 5: Get the Final End date for each project.

For getting the final end date for each project in every financial year we will need to compare financial year end date with its given end date to know weather it should be the financial year end date or its project end date it is expected to be finished.

Hence by using below expression we can get the final end date.

In our case, for project 101 the end date for financial year 2023-2024 will be 31^st March, 2024 (financial year end date). But for the same project its end date will be 15^th June,2026 (project end date) for the financial year 2026-2027.

Expression - ifCase( dateDiff( "m", Endate, FYEndYearDate )<0, Endate, FYEndYearDate )

Step 6: Get the number of pending months for each project.

Finally now by calculating the difference between the Final Start Date and Final End Date we will get the remaining months in each financial year of the projects respectively.
In our case, for project 101 we have 3 months remaining in the financial year 2023-2024.

We can get this using below expression.

Expression - dateDiff( "m", FinalEndDate, FinalStartDate )+1

Below are the steps to derive the financial year-wise pending months for each project in Smarten:

Step 1: Need to get the financial year date of the projects for calculating remaining financial months

For getting the financial year month we will add a year in the end date given for all the project. 

For instance, we have end date as 15^th June, 2026 for project 101, so financial year for this project will be 31^st March, 2027.

Below steps demonstrate to get financial year end date from the given end date.

i. Copy the Column End Date by right-click option on the column and then Rename the copied column (EndDate_1) to FY End Date from the manage column as shown as below .

ii. Transform the FY End Date column to add 1-year interval to it which will give us the financial year end date as mentioned below.

Right click on EndDateNew -> Transform  -> More -> dateAdd

In this function we need to give 3 arguments as below

Argument 1 - Choose year as we need to add a year to FY End Date.

Argument 2 - Choose static button and add 1 to it as we need add a year to the existing end date.

Argument 3 - Choose the column FY End Date to which we need to add a year.

 iii. Transform the data type of FY End Date column from timestamp to Date datatype by using right-click transform option.

Step 2: Adding a new column for getting the Current Date which will help in calculating the months which we have already spent on the project from the date the project started.

For adding the new column of current date right-click on any column and click on custom in Add column function as shown below

In the expression window of Add column option mention the name as Current Date of the new column and given expression as today() in the expression window.

Step 3: Join Financial Year Master Dataset (Child Dataset) to Base dataset using Spark SQL    

             Query

As we require to calculate the pending months for each financial year coming between the given start and end date of the projects we will need a master dataset having all the financial year start and end date.

So create a separate dataset with all the financial year start and end date as shown below

By joining this master dataset with the existing base dataset we will get all the financial year start and end dates falling between the project start and end date.

For instance, for project 101 start and end date are 1^st May, 2023 and 15^th June, 2026 respectively. So for calculating the pending months in financial years - 2023-2024, 2024-2025, 2025-2026, 2026-2027, we will join the financial year master data in the base dataset using the below mention query.

For achieving this first we will add the child dataset to the main dataset with the Add dataset option available in the toolbar menu.

Now with the help of Custom Query option we will join the dataset with the help of below given query which will help in achieving our result.

SELECT A.*,B.* FROM Project Pending Month A 

              LEFT JOIN Financial Year B ON A.CurrentDate<=B.FYEndYearDate AND   

              A.FYEndDate>=B.FYEndYearDate

Now we will get the dataset as below having all the financial year entry falling between the start

and end date of all the projects

 

Step 4:  Add column FinalStartDate using a custom expression

As we don’t have to consider the months we have already spent on the project. So with the help of below condition we will create a new column comparing the Current Date with the Financial year Start Date. 

              Right click on any column -> Add column -> Custom 

              Custom Expression :- ifCase( dateDiff( "m", FYStartYearDate, CurrentDate )<0, CurrentDate,    

                                               FYStartYearDate )

Step 5: Add column FinalEndDate using a custom expression

With the help of below condition we will create a new column comparing the Current Date with the Financial year End Date. 

              Right click on any column -> Add column -> Custom 

              Custom expression :-  ifCase( dateDiff( "m", Endate, FYEndYearDate )<0, Endate, FYEndYearDate )

Step 6: Add column PendingMonth using a custom expression

For getting the pending month we will take out the difference between the above final start date and end date.

              Right click on any column -> Add column -> Custom 

              Custom expression :-  dateDiff( "m", FinalEndDate, FinalStartDate )+1

Hence with the help of above steps we can calculate the months remaining for the projects on the basis of their start date and end date.