Forum Discussion
divide contact timeframes into contract years
Hello experts,
Looking for help with the follwing task:
From a list of contracts spaning over a number of years (example), create a list with contract names, calendar days and contract years.
| Contract Name | Start Date | End Date |
| Contract1 | 10/1/2021 | 9/30/2028 |
| Contract2 | 3/30/2022 | 3/29/2025 |
| Contract3 | 11/1/2021 | 10/31/2031 |
| Contract4 | 7/10/2018 | 7/9/2025 |
| Contract5 | 3/1/2019 | 2/29/2024 |
Contact years are defined as: for each contract, Year1 covers the dates between Start Date and (Start Date +364 or 365 days), Year2 is the next 364 or 365 days and so on. The output would look as in example:
| Contract Name | Calendar Day | Contract Year |
| Contract4 | 7/10/2018 | 1 |
| Contract4 | 7/11/2018 | 1 |
| Contract4 | 7/12/2018 | 1 |
| Contract4 | 7/13/2018 | 1 |
| Contract4 | 7/14/2018 | 1 |
Please see the attached sample file. I created a list of contract days and calendar dates, however calculating the contract year as (Calendar day minus Contract day) divided by 364 or 365 does not produce accurate results, because of the leap years. Looking for a better way to assign the contract years to calendar days.
Best,
Alex
4 Replies
- rajendraongole1
Super User
Hi Anonymous - Please find the attached pbix file
calculated column:
ContractYear =VAR StartDate = Sheet1[Start Date]VAR CurrentDay = MAX(Calendar[Date]) -- Aggregating the DateVAR DaysSinceStart = DATEDIFF(StartDate, CurrentDay, DAY)RETURN FLOOR(DaysSinceStart / 365, 1) + 1you can achieve the same in power query editor too by creating a custom column that uses the same logic to determine the contract year.Use Date.AddDays to generate a list of days.Apply logic similar to the DAX formula in a calculated column.
Hope this helps.
Hi rajendraongole1 ,
Many thanks for your prompt answer!
I shloud've mentioned I'm not experienced in DAX or power query. Could you please detail the logic related to leap years? I saw you created a table that identifies them, but I don't understand how are they accounted for in the ContractYear calculation.
I think the formula needs some tweeking: in the sample file, ContractYear result should be a number from 1 to 10, however the results are numbers from 9 to 13. As example, the first row in the image - showing start date 11/1/2021 and calendar day 11/1/2021 - should show ContractYear 1 as opposed to 10, as we are in the first year of contract.
Looking forward to hearing your thoughts on this topic.
Best regards,
Alex
- rajendraongole1
Hi Anonymous -Calculate the total number of days from the start date to the current date.Adjust the calculation for leap years by dynamically calculating the number of days in the year for each contract year.Calculate the contract year by dividing the total days by 365 (or 366 in the case of a leap year).
This formula should now return the correct contract year. For example, if the start date is 11/1/2021, and the current date is also 11/1/2021, it will correctly return ContractYear = 1
try below calculated column:
ContractYear =VAR StartDate = Sheet1[Start Date]VAR CurrentDay = MAX(Calendar[Date]) -- Aggregating the DateVAR DaysSinceStart = DATEDIFF(StartDate, CurrentDay, DAY)RETURN FLOOR(DaysSinceStart / 365, 1) + 1
