I have a dataset that has reporting_year, reporting_week, worked_hours, scheduled_hours as columns. reporting_year, reporting_week are integers. Attendance is calculated as sum({worked_hours})*1.0/sum({scheduled_hours})*1.0
Having said that, both reporting_year and reporting_week are added to the controls + filters so that we can deep dive. The parameters created for both of these fields are also integers. Both are dropdowns and have multiselect enabled (Multiple Values). 2025 is the static default value for reporting_year.
I have the below visual created. Now i am trying to add a new column that shows the previous year attendance for that week. So if 2025 is selected in the reporting_year the new column should show the 2024 attendance by week.
I have tried using lag, periodoverperiod and all but not getting the result. Any help is appreciated.
The below article explains how to deal with calculations related to Prior Time Periods for comparison; when you are dealing with date fields.
Your case though seems slightly different in the sense that you don’t really have a date as your Year is an integer. One of the options is to treat your year field as a date field (e.g. convert 2025 as Jan 01, 2025) in the calculation and then you can get your prior year by going an year back and getting the data for the same. The converting to date is the new part and the rest of the details of getting prior period data you can refer from the article above.
The other option is to simply get data in a calculated field by reducing the year by a minus 1 and get from the past year.
In either case you need to have calculated fields for your measures that get data for selected year in the parameter and prior year and the visual itself should not directly filter the data for the period selected as that will constrain the data available and you will not be able to get prior year data in that case.
Hi @pratkan,
It’s been awhile since we last heard from you, did you have any additional questions regarding your initial post or was the solution provided above helpful for your case?
If we do not hear back within the next 3 business days, I’ll close out this topic.
