Forum Discussion

aashton's avatar
aashton
Icon for Helper V rankHelper 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 for last week, they pick yes, and it runs from Monday the 27th - Sunday the 3rd.  How would I write the DAX for that?

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.

2 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper 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.

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Icon for Community Support rankCommunity Support
    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.