Forum Discussion

aashton's avatar
aashton
Helper V
4 years ago
Solved

Filter for Last Monday through Sunday

Hello, I want to add a filter to my report to allow the user to run the data for the last Monday through Sunday.  So if today is Wednesday the 6th, and the filter asks if they want to see the data f...
Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi aashton 

     

    You mean to say that no matter what day of the week it is, you should just filter the data for the whole of the previous week.

    There two tables in my pbix file, Table and Y/N tabel

     

    Try this measure:

    Measure =
VAR _YorN =
    SELECTEDVALUE( 'Y/N'[Y/N] )
VAR _todaynumber =
    WEEKDAY( TODAY(), 2 )
VAR _startday =
    TODAY() - _todaynumber - 6
VAR _enddate =
    TODAY() - _todaynumber
RETURN
    IF(
        _YorN = 1,
        IF(
            SELECTEDVALUE( 'Table'[Date] ) >= _startday
                && SELECTEDVALUE( 'Table'[Date] ) <= _enddate,
            1,
            0
        ),
        BLANK()
    )

     

    Reslut :

     

    I put my pbix file in the attachment you can reference.

     

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

Using relative date filtering gets close but, unfortunately, uses weeks starting on Sunday and ending Saturday.

 

To improve on this, it helps if you have a date table with a week offset column defined using weeks starting and ending on the days you choose.