Forum Discussion

Uka's avatar
Uka
Frequent Visitor
2 years ago
Solved

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

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

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

     

4 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    2 years ago

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

    • Uka's avatar
      Uka
      Frequent Visitor
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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.