Date Count and Hierarchy Showing Dates That Doesn’t Exist

[Lierreyy]

Hello experts,

I’m finding that the date hierarchy always shows all dates for the year even if most of those dates don’t exist in the column of the dataset. For example, if I have a simple data table called “Products” like this:

Product	Start Date
Apple	Thursday, January 5, 2023
Banana	Thursday, January 19, 2023
Lemon	Monday, February 6, 2023

when I make a date slicer with hierarchy, it is showing all dates for the year.

I have already turned off “Show items with no data”.

In addition to this, if I wanted to find the number of dates using the following formula,

Date Count = COUNTROWS(VALUES(Products[Start Date].[Date]))

the output is 365, even though I only have 3 unique dates.

Do you know why this is happening? How can I get the date hierarchy to display only the dates that actually exist and the date count to count the number of unique dates that actually exists in the table?

Thanks!

[peter_c]

I have this same issue. It's incredibly frustrating that there doesn't seem to be a simple way to resolve it, if there is even any way at all! 

[amitchandak]

@Lierreyy , Join this date with a date table and then use that option

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Lierreyy]

Hi @amitchandak,

Thanks for the response. I created a Date Table:

And I set up the relationship. However, it still shows all quarters and months even though the date table doesn't even have any of those dates.

The slicer above is based on the date table field. Is there anything else I need to do to have the slicer only show the quarters, months, and dates that has data? Again, "Show items with no data" is not checked.

Thanks.

[Spotto]

I'm having the same problem and I couldn't find a solution. 😞