Solved: Date hierarchy slicer, dynamic value

Spazia · ‎06-03-2020

Hello,

I have in my table date from May and June.

In the future this table will expand with the years.

I've a Hierarchical date slicer in which I display the year and the month. When I chose 2020, it shows all the month of the year from January to december.

Even if I only have date of May and June.

Does anyone know How I can manage to display only the month for which I have dates ?

Thanks for your help !

v-lili6-msft · ‎06-04-2020

hi @Spazia

That because you are using built-in date hierarchy in the slicer, it likes you drag it into table visual too.

So, you could custom create a year column, month column and day column, then drag them into slicer.

Result:

and here is sample pbix file, plase you it.

Regards,

Lin

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

View solution in original post

v-lili6-msft · ‎06-04-2020

hi @Spazia

That because you are using built-in date hierarchy in the slicer, it likes you drag it into table visual too.

So, you could custom create a year column, month column and day column, then drag them into slicer.

Result:

and here is sample pbix file, plase you it.

Regards,

Lin

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

az38 · ‎06-03-2020

@Spazia

I think it will be uncorrect ideologically if Hierarchical date slicer will hide a part of Calendar

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Spazia · ‎06-03-2020

@az38, I agree with you. But a slicer showing values for which there is no data behind is misleading for the end-users.

My objective is for 2020 I have the months of May and June. And in the end of the year I'll have all the months from May to December.

In January 2021, I'd like to be able to show only January for 2021.

If there is no possibility to make that, I could live with that and have in my slicer a concatenation between month & year.

Anonymous · ‎07-03-2022

I fully agree with you on the misleading point. Did you find a solution? I am also struggling with this. I have for example a whole report page where I pretty much reporting the same stuff - let's say sales numbers for different groups of products.

I have one general slicer to select the product group as in most cases, it is pointless to compare figures across product groups and do not want to make pages for each new product groups. So now because of this weird (default?) slicer behavior, I get all dates in my date slicers regardless of wether the product group was introduced this year or last year. Is there an easy way to fix this by now?

Cheers, AAB