Date Slicer Granularity - I'd like it to slice only at the year level

[tommy_g]

I have data which needs to be sliced only at the year level. I've modified my date to month 1, day 1, giving me "1/1/year". Also adjusted formatting to "YYYY". The client loves the Date Slicer, but it insists on using full "M/D/YYYY" with the slider.

I know I can set other options for year, but they don't look as slick, and client prefers "slick". (me too)

Desired result would be display of years only, with year increasing or decreasing.

[parry2k · Accepted solution]

@tommy_g As a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.

https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-ca...

Once you have a date dimension, just use Year from date dimension, and yes you will see the slider with the number column used in the slicer.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[amitchandak]

@tommy_g , if you have Year as number data type, you can use between range ?

[tommy_g]

Yes, year level works in the slicer. My original date table, which I probably created several years ago, year, month and days were defined as character values. I have no idea why, but when changing to integers I can use the slider option. The complication I ran into is obvious in retrospect: on the screen, 2019 looks the same whether text or numeric.

[tommy_g]

Digging deeper into my date table, I see that my year, month and day columns are set to "text" data type. 

I do not remember when or why i did this, investigating now.

Presumably the slicer prefers numbers as "between" values. I think my two decades of VB programming have led me to expect too much in terms of "between"!

[parry2k · Accepted solution]

@tommy_g yes, once you convert year to number and use it on the slicer, it will show slider and allows you to pick between years.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[tommy_g]

Updating my previous comments, the date slicer "between" sliders support both "official dates" at the granularity of day, AND numeric years when the year column is defined as a whole number. The advantage of using numeric "years" is the slicer then does not show extraneous month and day values.

[parry2k]

@tommy_g why not you use year in the slicer, you can still have the slider, wat's the point showing the date if you are always slicing on a year, just an idea.

[tommy_g]

Hey, good question!

The table is not currently linked to a data table, it's just a simple list.

I did try using my internal date table with the slicer as a test.

However it only provides for list and dropdown options, the desired "between" option for the sliders is not available.

As far as I can tell, the sliders are ONLY available with a simple date.

[parry2k · Accepted solution]

@tommy_g As a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.

https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-ca...

Once you have a date dimension, just use Year from date dimension, and yes you will see the slider with the number column used in the slicer.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[tommy_g]

"Once you have a date dimension, just use Year from date dimension, and yes you will see the slider with the number column used in the slicer."

I already have a marked data table. If I add the date field to the slicer I can use the "between" to activate the slider.

If I select anything from the date hierarchy the slicer will accept it but NOT allow use of the "between" sliders.

As far as I can tell, the slicer requires an actual data value (i.e. a column showing the date icon).

Perhaps you can clarify your comment "slider with the number column", I'm not sure what number you are referencing.