Showing current week and previous week that changes with respect to selected date in the date filter

[Uka]

Hi,

Please can you help me to achieve the table below. I wanted to show the data for Current week and Previous week, such that when a date is selected from the date filter, the selected date will appear under current week and previous Friday date will appear under previous week. Please note that our week ends on Fridays. 

So, with the table below, the selected date from the Date filter would be 17/11/23. 

then,  with the table below the selected date from the date filter would be 10/11/23. 

Many thanks in advance

[parry2k]

@Uka there are tons of videos/blog posts on this, have you done a google search for the solution?

[Uka]

Hi @parry2k ,

Thanks for your reply but I have spent the whole day searching for the solution but could not find any.

All the solutions I found uses no filter in the view as I explained above.

I can show the current week and last week without the Dynamic Date Filter requirement.

Please if you can look into it I would appreciate!

Thanks

[Anonymous · Accepted solution]

Hi @Uka ,

I think you can create a Date table to help calculation.

Date = 
VAR _STEP1 =
    ADDCOLUMNS (
        CALENDARAUTO (),
        "Year", YEAR ( [Date] ),
        "Month", MONTH ( [Date] ),
        "WeekDay",
            VAR _WEEKDAY =
                WEEKDAY ( [Date], 2 )
            RETURN
                IF ( _WEEKDAY >= 6, _WEEKDAY - 5, _WEEKDAY + 2 ),
        "DayName", FORMAT ( [Date], "DDDD" )
    )
VAR _STEP2 =
    ADDCOLUMNS (
        _STEP1,
        "WeekStart",
            [Date] - [WeekDay] + 1,
        "WeekEnd",
            [Date] - [WeekDay] + 7
    )
RETURN
    _STEP2

Relationship:

Measure:

Measure = 
VAR _SELECTDATE =
    SELECTEDVALUE ( 'DimWeekEnd'[WeekEnd] )
VAR _PREWEEKEND = _SELECTDATE - 7
RETURN
    IF (
        MAX ( 'Date'[WeekEnd] ) IN { _SELECTDATE, _PREWEEKEND },
        CALCULATE ( SUM ( 'Table'[Value] ) )
    )

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.

[Uka]

Hi @Anonymous ,

Many thanks for this solution.

It was very close to what I wanted, so I have modified to suit my situation.