Convert a date field to the format "YYYY-MM" AND keep it as a type "Date" (rather than "Text")

[Anonymous]

We often report on data based on month so I often convert my dates to YYYY-MM so I can build reports and graph using that field.

My business user would like the Filter for "YYYY-MM" to be a slider rather than a drop-down filter so users do not have to click so many boxes to get the desired period.

It appears that if the "YYYY-MM" field is formatted as text, it will not give me the slider option on the filter.

I tried converting the months to the first date of each month and keeping it as a "date" type field to build a slider (1/1/2019, 2/1/2019, 3/1/2019, etc.) That works as a slider. However it shows the dates to the day granularity in the slider which implies that the data can be filtered by day which it cannot. I only want the slider to show the MONTHS since users can only filter from Month A to Month B.

If a user selects this, it implies they are including the first 7 days of March only. However, since the March data is all listed under Start of Month = "3/1/2020" this means that ALL March data will be included.

So, my question is...is there a solution for creating a SLIDER filter in the format YYYY-MM OR which only shows distinct months on the slider options?

[Pragati11 · Accepted solution]

Hi @Anonymous ,

Click on your date column, move it to a slicer and select Date Hierarcy:

Then just Year ans Month, you will get the slicer as follows:

You can't achieve a slider/scroller for Month Year date format.

Thanks,

Pragati

[amitchandak · Accepted solution]

@Anonymous , You can mark YYYYMM as the number and you can use range. But that is not idle

or create as

Month Year = year([Date]) *100 + Month([Date])

Or use timeline slicer https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104380786?tab=Overview

[Pragati11 · Accepted solution]

Hi @Anonymous ,

Click on your date column, move it to a slicer and select Date Hierarcy:

Then just Year ans Month, you will get the slicer as follows:

You can't achieve a slider/scroller for Month Year date format.

Thanks,

Pragati

[Anonymous]

Thank you @Pragati11 . I understand the solution you are suggesting and I believe that will meet the user's needs.

However, something must not be working correctly for me. I have created the column "Start of Month" and it is formatted as a date field MM/dd/YY.

However, when I drag that field over to the filter and click "Date Hierachy", it ONLY shows me the year option. Normally it should show year, quarter, month and day and then you X out the ones you do not want to use.

Do you know why it would not be automatically showing me the full list of date options?

[Pragati11]

HI @Anonymous ,

Can you expand hierarchy of your START DATE column and share screenshot?

Thanks,

Pragati

[Anonymous]

This is unexpected behavior. When I drag the Start of Month to a table, it shows the full hiearchy and has all of the values checked in the "Fields" pane. See bottom table. 

However, when I drag Start of Month to a slicer, it only pulls the year. If I try to click on the month, quarter or day to add that to the slicer, it will not allow me to add that to the slicer.

Maybe it's a bug of some sort?

[Anonymous]

Thank you @amitchandak . I looked at the timeline slicer and that is exactly what I would like to be able to use. However, my company is on an older version of Power BI and that is not an option. I wasn't clear if that's a custom app that has to be downloaded but in my organization, I do not think that will be an option at this point so I will look at other workarounds.