Filtering previous workweek on a Power BI page

[ray_codex]

Hi,

I had a request by a customer that needed a relative datefilter that filters the previous work week, monday to sunday. Working with a tabular model in AS I knew a failure in the refresh would give a wrong result on a calculated column that could be used as a filter. We are using a live connection, so the only option is creating a measure. Since I couldn't find an easy solution on the forums I wanted to share this.

- Create this measure:

IsPrevWorkWeek =
VAR prev_monday =
    TODAY ()
        - ( WEEKDAY ( TODAY (), 2 ) + 6 )
VAR prev_sunday =
    TODAY () - WEEKDAY ( TODAY (), 2 )
VAR datesrange =
    CALENDAR ( prev_monday, prev_sunday )
RETURN
    IF ( SELECTEDVALUE ( 'date'[Date] ) IN datesrange, 1, 0 )

- Use the measure as a filter on the visuals you want to filter for the previous work, by adding it with a value that equals 1.

The advantage of this approach is that the measure isn't affected by any filter context and calculates itself on report interaction, so isn't relying on data refresh. The only thing of course to note is that the visual need to have the date in a row or axis.

[Villezekeviking]

You can make use of my DAX Calendar that has offset-columns for days, weeks, months, quarters and years from today for any kind of relative date filtering!

Just filter your data on to ony include -1 on the Week offset from this calendar table after you've created a relationship and you're done.

As long as you refresh the dataset daily, the calendar table updates with it.

You'll find it here:
https://www.villezekeviking.com/dax-tables-calendar-and-time/

[123abc]

For those who are interested in filtering the previous workweek on a Power BI page, you can create a measure called IsPrevWorkWeek using the following DAX formula:

IsPrevWorkWeek =
VAR prev_monday =
    TODAY ()
        - ( WEEKDAY ( TODAY (), 2 ) + 6 )
VAR prev_sunday =
    TODAY () - WEEKDAY ( TODAY (), 2 )
VAR datesrange =
    CALENDAR ( prev_monday, prev_sunday )
RETURN
    IF ( SELECTEDVALUE ( 'date'[Date] ) IN datesrange, 1, 0 )

You can then use this measure as a filter on the visuals you want to filter for the previous workweek by adding it with a value that equals 1. The advantage of this approach is that the measure isn’t affected by any filter context and calculates itself on report interaction, so isn’t relying on data refresh. The only thing to note is that the visual needs to have the date in a row or axis.

I hope this helps!

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.