Hi William! Try this:
[Date] <= DATEADD('week', -1, TODAY())
[Date] > DATEADD('day', -DATEPART('weekday', TODAY()), DATEADD('week', -1, TODAY()))
The trick here is using -DATEPART('weekday', TODAY()) as the offset, since that'll always shift back to the previous Saturday. We then use greater-than to start on Sunday.
Thanks for the quick reply! This works in principle, but i might not explained myself very well.
My dashboard splits multiple metrics this year vs last year for each week. However, when i filter by a week which is only partially complete, last years figures will include the whole week whereas this years will include only Monday to Wednesday (as an example).
My aim is to create a dashboard that can dynamically filter based on the weekday?
Hope this makes sense.
1 of 1 people found this helpful
In that case, what you should do is first filter on the week number, which I assume you've already done based on what you said.
Then, you should create a filter that cuts out all the days of the week that come after today.
DATEPART('weekday', [Date]) <= DATEPART('weekday', TODAY())
Add that to the filters shelf and set it to keep only TRUE.
With a slight bit of editing. (my business week runs Monday-Sunday) that works perfectly!