Date Slicer seems to remove the report filter Flag for last 3 years

[DebbieE]

I have a measure for rolling 3 year flag

Rolling 3 Year Flag = IF(DATEDIFF('Date'[Date].[Date],TODAY(),YEAR)<=3 && DATEDIFF('Date'[Date].[Date],TODAY(),YEAR)>=0,1,0)

this flag has been added to the entire report and set to 1 to make sure I only have information from these 3 years

If I add date as a table I get 2017 2018 2019 and 2019 as expected for a rolling flag.

However As soon as I change that table into a Slicer, all the years appear in the data set. right from 2000.

I dont understand why the filter stops being applied on the slicer

I have tried adding the filter on the Slicer visual but that doesnt make any difference either

How come its fine if the date is in a table but then the slicer shows all the dates?

[amitchandak]

@DebbieE , do use .date, unless you have timestamp

use only 'Date'[Date]  in place of 'Date'[Date].[Date]

Also you can restrict 3 years using relative date filter at page or report level

[DebbieE]

my filter contains Year and Month from the date hierachy

I add Date and relative date last 3 years as a filter in the slicer and it still doesnt work

[v-lionel-msft]

Hi @DebbieE ,

Does your data contain sensitive information? If you don’t mind, please share your .PBIX file.

Best regards,
Lionel Chen

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

[DebbieE]

Unfortuntely I cant share the pbix file. 

I have a fact table and a date table

When I have the date in a table it will filter no problems, with either a flag or relative filter

As soon as I change it to a slicer (Year and month hierarchy) the filters dont work any more

Ive noticed it throughout using Power BI and its becoming a real issue

[v-lionel-msft]

Hi @DebbieE ,

"As soon as I change it to a slicer (Year and month hierarchy) the filters dont work any more..."

Could you show us this problem with screenshots or animations?

I thought your visual effect was like this.

Best regards,
Lionel Chen

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

[DebbieE · Accepted solution]

All resolved

I hadnt marked my date table as a date table

I wasnt sure why I needed to do this, since you lose all the hierarchies under your dates

However I have now moved to a marked date table and now my Date Slicer works properly. You can apply a flag to it like current year and it all works because all the items in the date table are now viewed as Date information

[parry2k]

@DebbieE you have to use it as a visual level filter on the slicer, also you should add it is a column and then put it page level filter so all the visuals are filtered on this flag.

As a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.

https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-ca...

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[DebbieE]

you have to use it as a visual level filter on the slicer, also you should add it is a column and then put it page level filter so all the visuals are filtered on this flag.

I am using a date dimension as per the standard design recommendations

I did put the filter on the slicer and it made no difference

Im not understanding the second part of your response "also you should add it is a column and then put it page level filter so all the visuals are filtered on this flag"