KNOWLEDGE BASE

## Year-Over-Year Change Rate Based on Weekday Instead of Month Day

Published: 30 Nov 2022

### Question

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.

### Environment

• Tableau Desktop

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: 