Forum Discussion

aflintdepm's avatar
aflintdepm
Icon for Helper III rankHelper III
3 years ago
Solved

Trouble with Relative Date Filters

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 re...
Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    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's avatar
aflintdepm
Icon for Helper III rankHelper III
3 years ago

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's avatar
v-yueyunzh-msft
Icon for Community Support rankCommunity Support
3 years ago

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