I have a date table which has 4 columns - date, year, Month year, Quarter year. Month year has values in the format "mmm-yy" and I want to use the month year column in the in between slicer. I have also created another column 'Month year number' for sorting 'Month year' column.

You can’t use an “In between” slicer on a text column like mmm-yy. That slicer type only works on continuous values (Date or Number), not categorical text. What you could is: Use two slicers on Month year (text) One for Start Month One for End Month Apply the range logic in DAX

In between slicer for text values in the date column

9 Replies

Natarajan_M
Super User
8 months ago
Hi dikssha , The default slicer in PBI won't show between option for the text

if we make it as date with the formatting to MMM-YY still it will get default to the dd-MM-yyyy when we are selecting the between option

Did you get a chance to check the custom visual ?

timeline visual is close to your requirement
Thanks
maruthisp
Super User
8 months ago
Hi dikssha ,

Please check the attached pbix file with a solution for slicers with date as TEXT and can select in the slicers.

Please let me know if you have any further questions or need clarifications.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks!

Best Regards,

Maruthi

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/

X - Maruthi Siva Prasad - (@MaruthiSP) / X
In between slicer for text values in the date column.pbix56 KB
- dikssha
  Regular Visitor
  8 months ago
  Many thanks for the solution.
  However, it wil not serve the purpose because dropdowns are not as user friendly as the slicer. Also, if user select higher value in the first then it will not work. I know that I can create 2 tables and then I can make the first slicer to filter second one but since Power BI does not has a feature of automatic slicer reset, until user will change the option in the second slicer, visual will remain blank.
danextian
Super User
8 months ago
Hi dikssha

In-between/range slicers support numeric and dates only. In the query editor, create a column that returns a date equivalent of your MMM-YY column and use that in a slicer

Date.From( Text.Start([#"MMM-YY"],3) & " 1, 20" & Text.End([#"MMM-YY"],2) )
- dikssha
  Regular Visitor
  8 months ago
  If I do that, then in slicer it will show data as contnuous series due to which numbers in between 2 rows will also be displayed. For instance, if Dec 2012 is equal to 201212 and Jan 2013 is equal to 201301, slicer will display all numbers in between 201212 and 201301, which will not be user friendly. This is the same problem with date picker.
Natarajan_M
Super User
8 months ago
Hi dikssha , The default slicer in PBI won't show between option for the text

if we make it as date with the formatting to MMM-YY still it will get default to the dd-MM-yyyy when we are selecting the between option

Did you get a chance to check the custom visual ?

timeline visual is close to your requirement
Thanks
cengizhanarslan
Super User
8 months ago
You can’t use an “In between” slicer on a text column like mmm-yy. That slicer type only works on continuous values (Date or Number), not categorical text. What you could is:

Use two slicers on Month year (text)

One for Start Month

One for End Month

Apply the range logic in DAX
Anonymous
Not applicable
8 months ago
Hi dikssha

Thank you for reaching out to the Microsoft Fabric Forum Community.

cengizhanarslan danextian maruthisp Natarajan_M Thanks for the inputs.

I hope the information provided by users was helpful. If you still have questions, please don't hesitate to reach out to the community.
- Anonymous
  Not applicable
  8 months ago
  Hi dikssha
  
  Hope everything’s going smoothly on your end. I wanted to check if the issue got sorted. if you have any other issues please reach community.