Creating a dynamic data filter handling a string type

[rm1]

Hi All

I'm trying to create a dynamic default filter that can select all the Year-Month combinations before and equal to today.

My problem is that I have this field "Year Month" with format YYYY-MM and it's a string. And the Script is managed by IT department therefore I only can work with set analysis and filters without modifying anything in the data model. 

If I try a formula to filter the Year or the Month it works (look to the example as per attachment) but if I try to combine them is not working anymore.

I'd need to select all the Year-Month combinations prior to actual month.

Any suggestion on how to create some kind of concatenation that can do the Job?

thanks in advance for any help!

[justISO]

Hi,

have you tried to convert your [Year Month] string to date and work in filter with that? I would try something like this:

{<Date(Date#([Year Month],'YYYY-MM'))= {"=Date(Date#([Year Month],'YYYY-MM'))=monthstart(today())"}>}

Red marked '=' signs you can modify to '<=' to get past dates.

[rm1]

I've tried to copy your formula inside my filter but it's still not working

[justISO]

Please check and adjust this formula with correct dimension name. Name or format maybe differs.  From your screenshot seems it should be called [Month Year] not [Year Month] as I wrote.