Trouble with Relative Date Filters

[aflintdepm]

I am having a lot of difficulty getting a report set up with the proper relative date filter.

Scenario:

I have a date table where I have a business week that runs Monday->Sunday

I would like my report to always show the current week (Mon->Sun) based on End of Week.  This means that, thru Sunday at midnight, it will show this week's data, and at 12:01 am on Monday, the report will revert to blank until new data is available.

When I set a relative filter in the Filter pane, using my Mon->Sun week as the filter, it does not align with the defined week- it reverts to Sun->Sat, using a standard calendar week.  The same thing happens if I put the same attribute in a slicer set to "This Week"

How can I get the filter or slicer to lock onto Mon->Sun?  Ideally, I would like to create a duplicate page with this same view, but for the prior week (last 1 week).

[v-yueyunzh-msft · Accepted solution]

Hi , @aflintdepm 

According to your description, you want to set a relative filter in the Filter pane, using my Mon->Sun week as the filter and this is judged by the TODAY.

I think you can try to use the "Preselected Slicer" Custom visual in Power BI to realize it .

Then we need to click "Enter Data" to create a table 'Flag' like this:

Then we can create a measure like this:

Measure = var _today = TODAY()
var _weekday = WEEKDAY(_today,2)
var _monday = _today-_weekday+1
var _sunday = _monday+7
var _cur_date = SELECTEDVALUE('Table'[Date])
return
IF(_cur_date>= _monday && _cur_date <_sunday ,TRUE() ,FALSE())

Then we can put the fields we need on this slicer fields.

This Slicer will automatically change the default selection value based on your data.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[aflintdepm]

@v-yueyunzh-msft  I have worked thru both of your samples with the following outcome:

The additional visualization (Preselected Slicer) did work, but it does not appear to support multiple versions in the same workbook.  Meaning, when I set it up with the Current Week measure, then try to make a new page using the same visualization for Last Week, they seem to confilict with each other.  Not sure if you've seen this behavior in your use?

[v-yueyunzh-msft]

Hi,  @aflintdepm 

Thanks for your quick response! I test in my side , it seems work good in different report page.

If you still have the issue to show , can you give me your sample .pbix file (without sensitive data) and your screenshot with this issue to explore?

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[aflintdepm]

@v-yueyunzh-msft 

Thank you for the detailed steps.  This did work for the problem I was having.  I have 1 follow up question.  If I wanted to offset this for "last week", how would I change the measure?

[v-yueyunzh-msft]

Hi, @aflintdepm 

 Thanks for your quick response! You can use this Dax to filter the Last Week :
Last Week = var _today = TODAY()
var _weekday = WEEKDAY(_today,2)
var _monday = _today-_weekday+1-7
var _sunday = _monday+7
var _cur_date = SELECTEDVALUE('Table'[Date])
return
IF(_cur_date>= _monday && _cur_date <_sunday ,TRUE() ,FALSE())

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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