topic Filter month year column to exclude past dates from slicer in DAX Commands and Tips

Filter month year column to exclude past dates from slicer

Anonymous — Sun, 08 Nov 2020 11:54:34 GMT

I have a dates key table that runs from Jan 2018 - Jan 2021.

I have a month year column (Text Format) so it doesn't display data past today:

On or Before Today = 'DimDate'[DateS] <= EOMONTH(TODAY(),0)

Is there a way to have a slicer's past dates respected when it changes?

e.g. When I slice the last 24 months I get this

However when I slice the last 2 months I still get the begining dates of slicer:

Something along the lines of this???

On or Before Today = ALLSELECTED('DimDate Sort'[Dates]) >= 'DimDate'[DateS] <= EOMONTH(TODAY(),0)

Thanks in advance

*CAVEAT - I have a DimDate table for counts and a DimDate Sort to build relationships off in case you're wondering

Re: Filter month year column to exclude past dates from slicer

Fowmy — Sun, 08 Nov 2020 13:07:04 GMT

Anonymous

Relative Date slicer always takes the current date to calculate the dates, that cannot be changed.

What is your expected output when you select a range?

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube LinkedIn

Re: Filter month year column to exclude past dates from slicer

Anonymous — Sun, 08 Nov 2020 13:34:06 GMT

Fowmy I'm hoping to get an expected output like this if I cast the slicer date range at last 2 months:

Re: Filter month year column to exclude past dates from slicer

Fowmy — Sun, 08 Nov 2020 13:42:36 GMT

Anonymous

I am not sure how you have set up your model and the relationships. Also, The measure you are using can be another point to check. If you could share a sample PBIX file, it's possible to identify the cause.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube LinkedIn

Re: Filter month year column to exclude past dates from slicer

amitchandak — Mon, 09 Nov 2020 02:31:35 GMT

Anonymous , Following this video, if you want to select date/month and then have rolling month.

https://www.youtube.com/watch?v=duMSovyosXE

You can also refer this for List of value

https://community.powerbi.com/t5/Desktop/Required-custom-date-Slicer-Last-7-days-last-15-days-last-30/m-p/1284966#M561629