Forum Discussion
Filter Relative date
Hello,
I need to filter my visuals using Filter type `Relative date` for the previous week. I want to set week as starting on Monday and ending on Sunday. Despite having set `Local for import` for Poland and having my Regional settings set to Poland and France, I still have Power BI filters saying that week starts on Sunday and ends on Saturday. I would be grateful for helping in this matter.
Hi Anonymous
This is actually a long standing bug - https://community.fabric.microsoft.com/t5/Issues/There-is-a-bug-in-the-relative-date-slicer-incorrect-calendar/idi-p/1651800
Alternatively, you can create a calculated column that returns a week number relative to today's date and use this calculated column to filter your visuals.
Week Number from Today = VAR PreviousSunday = VAR CurrentDate = TODAY () -- Get the current date VAR DayOfWeek = WEEKDAY ( CurrentDate, 2 ) -- Determine the day of the week (1 = Monday, 7 = Sunday) VAR Result = IF ( DayOfWeek = 7, -- If today is Sunday, return today CurrentDate, -- Otherwise, subtract the day of the week to get the last Sunday CurrentDate - DayOfWeek ) RETURN Result VAR _DIFF = DATEDIFF ( CalendarTable[Date], PreviousSunday, DAY ) + 1 -- Calculate the difference in days between the given date and the last Sunday VAR _ROUNDEDUP = ROUNDUP ( DIVIDE ( _DIFF, 7 ), 0 ) -- Convert the day difference into weeks, rounding up to the nearest whole number RETURN IF ( _ROUNDEDUP >= 1, _ROUNDEDUP, 0 ) -- Ensure the result is at least 1; otherwise, return 0
2 Replies
Hi Anonymous
This is actually a long standing bug - https://community.fabric.microsoft.com/t5/Issues/There-is-a-bug-in-the-relative-date-slicer-incorrect-calendar/idi-p/1651800
Alternatively, you can create a calculated column that returns a week number relative to today's date and use this calculated column to filter your visuals.
Week Number from Today = VAR PreviousSunday = VAR CurrentDate = TODAY () -- Get the current date VAR DayOfWeek = WEEKDAY ( CurrentDate, 2 ) -- Determine the day of the week (1 = Monday, 7 = Sunday) VAR Result = IF ( DayOfWeek = 7, -- If today is Sunday, return today CurrentDate, -- Otherwise, subtract the day of the week to get the last Sunday CurrentDate - DayOfWeek ) RETURN Result VAR _DIFF = DATEDIFF ( CalendarTable[Date], PreviousSunday, DAY ) + 1 -- Calculate the difference in days between the given date and the last Sunday VAR _ROUNDEDUP = ROUNDUP ( DIVIDE ( _DIFF, 7 ), 0 ) -- Convert the day difference into weeks, rounding up to the nearest whole number RETURN IF ( _ROUNDEDUP >= 1, _ROUNDEDUP, 0 ) -- Ensure the result is at least 1; otherwise, return 0