Date Slicer displaying non-existent weeks

[JeppeKarlsen]

Hello Power BI community.

Im currently creating a dashboard in which a date slicer can be operated to filter for certain dates. The data behind is coming from a calculated column that is formatted as yyyyww, and is calculated as following:

WeekYearNumeric =

VAR YearValue = YEAR([Date])

VAR WeekValue = WEEKNUM([Date], 21)

RETURN

YearValue * 100 + WeekValue

It is set up like above because the slicer only works when numeric values are used.
Below is a picture of said slicer.

Now to the problem:
Because the slicer setting is set to between (the desired setting for the costumer, since he wants to be able to drag to a certain date/week), the slicer doesnt go from e.g. 202653 (last week of 2026) to 202701 (first week of 2027), but instead desides to fill in the missing numbers from 202653 to 202701, essentially creating non-existing weeks (e.g 202689, 202697, and so on). These "dates" (every number between 202653 and 202701) does not exist in the data, so the slicer sort of "creates" them to fill out the space between 202653 and 202701. See picture below for example.

Is there a way to make the slicer NOT include these dates between e.g 202653 and 202701, so that it correctly selects the weeks only in the data? So when sliding the slider, it jumps from 202653 to 202701. 

And now to the longshot:
If someone is able to fix the above mentioned question with ease, i have another problem that needs fixing:
Is it possible to display the week and year in a different format in the two boxes above the slider itself? So instead of the format being yyyww, it can be something like yyyy-ww. I cant figure it out, since the data when formatting as yyyy-ww has the data type of text not numerical values. 
I can create a sort of easy-fix, with creating a card on top of the slicer, covering the two boxes, and then the card showing the correct format.

Kind Regards,
Jeppe K

[Anonymous]

Hi All
Firstly  LuukvdDoel  thank you for you solution!
And @JeppeKarlsen  ,in our practical experience, when the slicer uses the between function, the values must be continuous and not intermittent.

If you want to select intermittent values, you can choose other filter styles that can help you accomplish your needs.

For your second question, you want to set two different formats in the slicer, unfortunately this is also difficult to achieve, because when you use a field reference to the slicer, the value of the field can only be of one type, there can not exist more than one type of value stored in a field, and when our data is a text value, as we have said earlier, he can be filtered intermittently, so in the There is no way to use between for text values in slicer.

I would be grateful if my answer could help you solve your problem!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[LuukvdDoel]

The reason you are seeing weeks that shouldn't exist is because the data type of your output is an integer, which will simply count up.

What you are making is a label, essentially. I suggest trying to concatenate these two numbers as a string and then using the dropdown slicer type with multi select. You could even make two columns, one for the year and one for the week number and then combine them into a hierarchy on the datamodel tab. That was you can select years in total and drill down in the slicer into the weeks.

In general if you're assigning labels to a row, you want it to be a text datatype and if you're assining values to a row (which you want to use for calculations), you want it to be a number datatype.

[JeppeKarlsen]

Thank you for the answer LuukvdDoel 🙂

I have now tried messing around with my dashboard, but to no luck. I can create a dropdown menu where everything works, but the costumer/user was very set on it being a slider (as seen in the pictures in original post). Is there just no way to make a slider that doesnt "fills" out like shown?

Thanks again 🙂