Forum Discussion
Help with filtering Date Hierachy
Hello,
I am having trouble around creating a date slicer.
I have a date table that starts from June 25 (As I have no data earlier than that).
This works as a slicer but only monthly data is relevant so I need to group it. When I group it however, the date hierachy shows options from the start of the year that I don't want to be visible. Putting a date filter on the visual also doesn't seem to work.
How can I create a a date slicer, in for format [Year][Month], that doesn't show options where I have no data?
Kind regards,
Hi,
If you create a Calendar table using this DAX calculated table formula, then you will not face this problem
Calendar = calendar(min(Data[Date]),max(Data[Date]))
Also, in the Calendar table, create calculated column formulas for Year, Month name and Month number. Sort the Month name column by the Month number. To your visual, drag Year and Month name from the Calendar table.
5 Replies
This is either because you are using the auto generated date hierarchy and some rows do not have a value or you're using a calendar table and there are dates in the fact table which are blanks or not covered by the calendar table. The simplest method is to add another visual filter, go to advanced settings and select not blank.
If you're using Auto date/time, please read this - https://data-mozart.com/tiq-part-1-how-to-destroy-your-power-bi-model-with-auto-date-time/
Hi,
If you create a Calendar table using this DAX calculated table formula, then you will not face this problem
Calendar = calendar(min(Data[Date]),max(Data[Date]))
Also, in the Calendar table, create calculated column formulas for Year, Month name and Month number. Sort the Month name column by the Month number. To your visual, drag Year and Month name from the Calendar table.
This is how I have it setup currently, with Calendar(Min,Max) generating the table.
Wonder if there is a way to make this work with Automatically generated date heriachy rather than creating extra columns for Year/month.
Other reply of Filter by not blank didn't work. This was tested with no fact table, only a caleandar table.
You must create a Calendar table.
Hi CM12345Office,
Has your issue been resolved?
If the response provided by Ashish_Mathur, danextian addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.
Thank you.
