Answer
Please calculate the weekday difference of years and make a YOY calculation based upon it.
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:
