Is it possible to display the date as YYYYMM using segmentation and between so that the MM goes only between 01 and 12?

[Anonymous]

Hello everyone

I was wondering if it is possible to use a column that includes YearMonth; YYYYMM like 202001, 202002, etc. in a cutter with Between as an alternative, so that the part of the month (MM) goes only between 01 and 12 and no more.

When I use the Enter option in the slicer, the month numbers range from 01 to 99 even if those numbers are not present in the column, that is, the numbers are displayed as continuous and not categorical! See the screenshot for an example of what it looks like:

[Anonymous · Accepted solution]

@Anonymous 

There are many ways to get YearMonth, other than the format(), you can also use Concatenate and then change it to whole number type. Or the simplest way is just change the date format to YYYY-MM. 

Yearmonth = CONCATENATE(YEAR([Date]),MONTH([Date])) 

But it is impossible to only show Month number when you use it as slicer. You must also create a month column as a slicer.

Month = MONTH([Date]) 

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , If you create it as the number it is. But between slicer will not stop the user to enter a value like 201213

YYYYMM = format([Date],"YYYYMM")*1

Better you use a custom visual timeline

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