I am trying to calculate the % of time lapsed over a year (split into 3 cycles per year/ 4 month cycles)
I am using the current expression but the calculation is not correct. This needs to be a dynamic calculation that works on the percentages based on todays date comparing to the start of the 4 month cycles.
DATEDIFF('day',DATETRUNC('month',today()),today())
/
DATEDIFF('day',DATETRUNC('month',TODAY()),DATEADD('month',4,DATETRUNC('month',TODAY())))
How it should be working is
Eg. Today 21st March 2022
Cycle started on the 1st January 2022
Cycle lasts for 4 month
Days in cycle - 120
Days completed so far in the cycle - 80 (today being 21st March)
So the % time lapsed should be 66.6666667%
Good day Graham. Interesting, 4 month cycle? 1/3 of a year?
Normally if it was quarters or months, I'd use DATETRUNC to roll back to the start of the period in question (year or quarter or month). But since this is "Thirds" you should probably set up a calculation to determine the start of period of the current Third:
Third Start Dt:
DATE(DATEADD('month'
,4*INT((MONTH(TODAY())-1)/4)
,DATETRUNC('year',TODAY())
))
Once you have that, you can do the DATEDIFF:
% Complete of Third:
// difference between Thirds start date and today
DATEDIFF('day',[Third Start Dt],TODAY())
/
// difference between Thirds Start Date and Thirds End Date
DATEDIFF('day',[Third Start Dt],DATEADD('month',4,[Third Start Dt])
For a sampling of dates this yields:
I've attached an example workbook for dates for the past year. Does this help?
Best regards,
-Dan
