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.
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 Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much, Anonymous! This was very helpful and I ended up doing something similar to your suggestion. I have a calendar table, so I just created a conditional column that assigned 1-7 to each respective day of the week. I then used the Relative Date Slicer for in current week/in previous calendar week and overlaid the weeknum conditional column that I had built (Monday = 1, etc.). Your suggestion helped immensely - thank you!