Filter for Last Monday through Sunday

[aashton]

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?

[v-chenwuz-msft · Accepted solution]

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]

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.