Replace CURRENT_DATE in formula to get previous year
Summary
Head count by length of service reportContent
We currently have a report called Headcount by Length of Service. I am trying to pull the previous years (2019) data but because the formula for Length of Service has "CURRENT_DATE" the numbers are not pulling correctly. Is there a way to get this report to pull the data without looking at the current date and pull it as of the year?
I will attach the XML for reference to report.
CASE WHEN YEAR(CURRENT_DATE) - YEAR("Worker"."Employee Hire Date") - (CASE WHEN (CAST(MONTH(CURRENT_DATE) AS INT)*100) + CAST(DAY(CURRENT_DATE) AS INT) < (CAST(MONTH("Worker"."Employee Hire Date") AS INT)*100) + CAST(DAY("Worker"."Employee Hire Date") AS INT) THEN 1 ELSE 0 END) <2 THEN '0-1 Years' WHEN YEAR(CURRENT_DATE) - YEAR("Worker"."Employee Hire Date") - (CASE WHEN (CAST(MONTH(CURRENT_DATE) AS INT)*100) + CAST(DAY(CURRENT_DATE) AS INT) < (CAST(MONTH("Worker"."Employee Hire Date") AS INT)*100) + CAST(DAY("Worker"."Employee Hire Date") AS INT) THEN 1 ELSE 0 END)