When a filter based on this custom column is active, the dashboard will reference the last working day.
If the dashboard is checked on Monday or Sunday, Friday's data will be shown.
If the dashboard is checked on Tuesday - Saturday, the prior day will be shown.
Apply this SQL script to a custom column of a DateDim table and apply a filter based on this new column to the dashboard set to 1.
ifint(
[Date] =
case
when DayOfWeek(CurrentDate()) in (2,3,4,5,6) then adddays(createdate(getyear(now()), getmonth(now()), getday(now())), -1) -- if the day is between Tuesday and Saturday go back return yesterday
when DayOfWeek(CurrentDate()) = 1 then adddays(createdate(getyear(now()), getmonth(now()), getday(now())), -3) -- if the day is a Monday go back 3 days for last friday
when DayOfWeek(CurrentDate()) = 7 then adddays(createdate(getyear(now()), getmonth(now()), getday(now())), -2) -- if the day is a Sunday go back 2 days for last friday
end
, 1, 0)
