Filter Relative date

[Anonymous]

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.

[danextian · Accepted solution]

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

[Anonymous]

Hello, thank you for an answer! 🙂