I need to compare data from the previous week of the current week (with each week starting on Monday) to the same week of the previous year.
This is the expression I have for the first day of the week for the previous week is : =Date()-(6+Weekday(Date(),2))
for the last day of the previous week is: =Date()-Weekday(Date(),2)
I know that I can get the previous year with this function: =DateAdd("yyyy",-1,Date()-Weekday(Date(),2))
This is what I use to get data that occurred in the previous week: =Count(IIf((Date()-(6+Weekday(Date(),2))) Between (Date()-(6+Weekday(Date(),2))) And (Date()-Weekday(Date(),2)),1,0))
I attempted to use the same function for the previous year by adding =DateAdd("yyyy",-1,Date()) to each 2022 date, but when I run my report, I'm still seeing data calculated from 2022 and not 2021. How can I return the counts for the same week in the previous year?
This is what I tried last: =Sum(IIf(DateAdd("yyyy",-1,Date()-(6+Weekday(Date(),2))) BETWEEN DateAdd("yyyy",-1,Date()-(6+Weekday(Date(),2))) AND DateAdd("yyyy",-1,Date()-Weekday(Date(),2)),1,0))
How would this affect the leap year? (cross posted)
This is the expression I have for the first day of the week for the previous week is : =Date()-(6+Weekday(Date(),2))
for the last day of the previous week is: =Date()-Weekday(Date(),2)
I know that I can get the previous year with this function: =DateAdd("yyyy",-1,Date()-Weekday(Date(),2))
This is what I use to get data that occurred in the previous week: =Count(IIf((Date()-(6+Weekday(Date(),2))) Between (Date()-(6+Weekday(Date(),2))) And (Date()-Weekday(Date(),2)),1,0))
I attempted to use the same function for the previous year by adding =DateAdd("yyyy",-1,Date()) to each 2022 date, but when I run my report, I'm still seeing data calculated from 2022 and not 2021. How can I return the counts for the same week in the previous year?
This is what I tried last: =Sum(IIf(DateAdd("yyyy",-1,Date()-(6+Weekday(Date(),2))) BETWEEN DateAdd("yyyy",-1,Date()-(6+Weekday(Date(),2))) AND DateAdd("yyyy",-1,Date()-Weekday(Date(),2)),1,0))
How would this affect the leap year? (cross posted)