Forum Discussion
DOW Filter Mon, Tue, Wed... Each on Its Own Visual (Current Week)
- 5 years ago
You might be able to use a relative date slicer for this too using this calendar week.
- Anonymous5 years ago
Hi samdep
I think you need to build calendar and add a custom weeknum column and year column in it. If you use weeknum directly, you will get wrong weeknum at the end of the previous year and the beginning of the next year.
For example:
In my Sample I let week start on Sunday.
2020/12/31,2021/01/01,2021/01/02 are in the same weeknum, but weeknum function will give you 53 in 2020 and 1 in 2021. It's wrong result.
Firstly build a calendar table by dax and add calculated columns in it to get correct weeknum and year.
Date = ADDCOLUMNS ( CALENDAR ( DATE ( 2019, 01, 01 ), DATE ( 2021, 12, 31 ) ), "Year", YEAR ( [Date] ), "Month", MONTH ( [Date] ), "WeekNum", WEEKNUM ( [Date], 1 ), "WeekDay", WEEKDAY ( [Date], 1 ), "MonthName", FORMAT ( [Date], "MMMM" ), "DayName", FORMAT ( [Date], "DDDD" ) )Calculated columns:
ISO 8601 WeekNum = VAR _COUNT0 = CALCULATE ( COUNTROWS ( 'Date' ), FILTER ( 'Date', 'Date'[Year] = EARLIER ( 'Date'[Year] ) && 'Date'[WeekNum] - 1 = 0 ) ) VAR _BASENUM1 = IF ( _COUNT0 < 7, 'Date'[WeekNum] - 1, 'Date'[WeekNum] ) VAR _ISO_8601_WeekNum = IF ( WEEKDAY ( DATE ( 'Date'[Year] - 1, 01, 01 ) ) <> 1 && 'Date'[Year] = 'Date'[Year] && _BASENUM1 = 0, WEEKNUM ( DATE ( MIN ( 'Date'[Year] ), 12, 31 ), 1 ) - 1, _BASENUM1 ) RETURN _ISO_8601_WeekNumWeekYear = VAR _COUNT0 = CALCULATE ( COUNTROWS ( 'Date' ), FILTER ( 'Date', 'Date'[Year] = EARLIER ( 'Date'[Year] ) && 'Date'[WeekNum] - 1 = 0 ) ) VAR _BASENUM1 = IF ( _COUNT0 < 7, 'Date'[WeekNum] - 1, 'Date'[WeekNum] ) RETURN IF(_BASENUM1 = 0,'Date'[Year] -1,'Date'[Year])Then you can relate this date table with your Data table by date columns.
Add a Flag column in your Data table.
Flag = VAR _TodayWeeKNum = CALCULATE ( MAX ( 'Date'[WeekNum] ), FILTER ( 'Date', 'Date'[Date] = TODAY () ) ) VAR _TodayWeeKYear = CALCULATE ( MAX ( 'Date'[WeekYear] ), FILTER ( 'Date', 'Date'[Date] = TODAY () ) ) RETURN IF ( RELATED ( 'Date'[WeekNum] ) = _TodayWeeKNum && RELATED ( 'Date'[WeekYear] ) = _TodayWeeKYear, 1, 0 )My Date Table:
Add Flag column into Page Level Filter Field and set it to show items when value equal to 1.
Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You might be able to use a relative date slicer for this too using this calendar week.
Thank you for this post! For anyone who stumbles on this and is looking for an answer/idea here, I ended up assigning weeknum to each day of the week in my calendar table via a conditional column - and then, I used the relative date slicer (in current week) and overlaid that weeknum column (Mondays = 1, Tuesdays = 2, etc.) as a filter and it worked perfectly. Thanks again!