Select An Org
Year-Over-Year Change Rate Based on Weekday Instead of Month Day
Publish Date: Feb 24, 2026
Task
How to calculate the year-over-year change rate which is based on a day of the week instead of using the same day for the different years.
Typically a year-over-year change is based on a date such as 12/10/2022 and the calculation would be SUM(12/01/2022 - 12/10/2022)/Sum(12/01/2021-12/10/2021).
This solution is based on a day of the week instead of a specific date. For example, if this year's data is from Thursday (12/1/2022) to Saturday (12/10/2022) and we want to use last year's data from the same weekday, it would be Thursday 12/2/2021.
Steps
Here are the steps that result in the attached sample twbx.
1. Make a Parameter Select a Day to choose the month-to-date end day.
2. Make a calculation Weekday difference to get the weekday difference between this year and last year using the following:
IF ISOWEEKDAY(DATETRUNC('month',[Select a Day]))-ISOWEEKDAY(DATEADD('year',-1,DATETRUNC('month',[Select a Day]))) > 0
THEN ISOWEEKDAY(DATETRUNC('month',[Select a Day]))-ISOWEEKDAY(DATEADD('year',-1,DATETRUNC('month',[Select a Day])))
ELSE 7+ISOWEEKDAY(DATETRUNC('month',[Select a Day]))-ISOWEEKDAY(DATEADD('year',-1,DATETRUNC('month',[Select a Day])))
END
3. Make a calculation MTD SUM to get the month-to-date value for this year using the following:
IF [Order Date]>=DATETRUNC('month',[Select a Day]) AND [Order Date]<=[Select a Day]
THEN [Quantity]
END
4. Make a calculation MTD SUM with same Weekday to get the month-to-date value of last year using the following:
IF [Order Date]>=DATETRUNC('month',DATEADD('year',-1,[Select a Day]))+[Weekday difference] AND [Order Date]<=DATEADD('year',-1,[Select a Day])+[Weekday difference]
THEN [Quantity]
END
5. Make a calculation YOY change to get the YOY change rate using the following:
SUM([MTD SUM]) / SUM([MTD SUM with same Weekday])
The resulting year-over-year change based on a day of the week:
Additional Resources
Knowledge Article Number
001464367
Did this article solve your issue?
Let us know so we can improve!
