Forum Discussion

Quiny_Harl's avatar
Quiny_Harl
Icon for Advocate III rankAdvocate III
4 years ago

Filters on date slicer do not work as expected

Hello,

 

I have an issue with how filtering on date slicer works which I consider to be a bug in Power BI but I would like to have your opinion. 

Let's say I want to set a relative date filter on my date slicer so that it shows last one year.

After I apply the filter on the date slicer, we can see that it shows last one year but the visuals are simply not affected by it (FYI - the date in the visual is in the YYYYMM format). If I choose different dates from the slicer, the visuals are filtered but at the moment I return to the original dates, the visuals are again not affected by the date slicer! As you can imagine, If I want to post the report to Power BI Service with the dates in the last one year this becomes a problem.


Another very similar case related to this issue. I had a request the report to show last 2 years of data by default starting from the end of previous month. This should be the default setting and then the users should be able to choose another date range if they want to.
This can be achived with the following measure:

Default Date Range =
VAR start_date = EOMONTH(TODAY(),-25) + 1
VAR end_date = EOMONTH(TODAY(),-1)
VAR result = IF (
SELECTEDVALUE('DATE'[Date]) >= start_date && SELECTEDVALUE('DATE'[Date])<= end_date,
1,
0
)
RETURN result

Then I put this measure as a filter on my date slicer and set it to show items when  value is 1:

As we can see, the date slicer is filtered properly but the visual below is not affected by it. 
I'm kind of clueless on how to tackle this problem.

General Comment
Need Help

8 Replies

  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Quiny_Harl ,

     

    This behavior is by design. There are 2 aspects collaborating together:

     

    1. The relative date filter applies only to the values that are selectable in the slicer. That filter won't propagate anywhere else.

     

    2. A behavior of the range slicer is that if the customer does not move the slider at both ends, as shown in the figure below, there is effectively no filter applied.

     

     

    So if you never changed the ends of the range, either by dragging the slider or typing in a value, or if you selected the values that are the Min or Max, the filter won't do anything in actuality. This is to allow scenarios where one of the ends keeps changing.

     

    I would recommend you use the same relative date filter to other visuals.

     

    Referencing: https://community.powerbi.com/t5/Desktop/MAX-Date-wrong-Strange-MAX-DATE-behavior/td-p/2258170 

     

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Quiny_Harl's avatar
      Quiny_Harl
      Icon for Advocate III rankAdvocate III
      4 years ago

      v-kkf-msft Please, reread my post again, especially the part where I explain how "if I choose different dates from the slicer, the visuals are filtered but at the moment I return to the original dates, the visuals are again not affected by the date slicer!". I don't think this is by design because when I move the slicer to return to the dates that represent last X years of data, the visuals show all the data and are not affected by the date slicer.

      • ngaonkar's avatar
        ngaonkar
        New Member
        2 years ago

        I am facing the same issue. When the relative filter is set to (eg. last 6 months), the report does not filter by date. Only when I go and pick a certain date, the filter is applied. Is there a way to workaround this? Only customerName is showing under filters not the date range as you can see below.

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    It is likely working as designed. Hover over the filter icon to see which other filters contribute to the current filter context.  Most likely you have chosen a date field not from the calendar table but from the fact table.  Hard to tell without seeing the data model.

  • vesc's avatar
    vesc
    New Member
    4 years ago

    Hi,

    I have exactly the same problem. When there is a filter on the date slicer and I dont limit the data in the slicer as such the filter does not cascade down to the other visual. And yes, me interactions are set correctly.

    Any news on that issued?