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!

[AnotherJim · Accepted solution]

Has anyone found a solution to this?  I created a work-around that might work for you, but it requires a little setup.  Basically I added 4 new columns in my data model, 1 for each date component and then 1 column to sort the month logically (rather than alphabetically).  In my situation, PhysicalDate is the date column in the data model table named MPO Receipts:

     Physical Year = YEAR('MPO Receipts'[PhysicalDate])

     Physical Month = FORMAT(DATE(1, MONTH('MPO Receipts'[PhysicalDate]), 1), "MMM")

     Physical Day = DAY('MPO Receipts'[PhysicalDate])

     Physical MonthSort = MONTH('MPO Receipts'[PhysicalDate])
Note: If you want the month name to be spelled out, just format with 4 Ms: "MMMM".

I sorted Physical Month by the column Physical MonthSort. 

I added the 3 date element columns to a slicer in the hierarchy of Year-Month-Day and it seems to work fine for me.  The slicer options are limited to only the dates in my model.  So far it's working as I want it to.


Note: If you also need the quarter date element in your slicer, just add another column to your model and then in the slicer:

    Physical Quarter = "Qtr " & QUARTER('MPO Receipts'[PhysicalDate])

Alternatively, these columns can be created in the source outside of Power BI, if your source allows it.

I'm interested to know if this helps anyone.

[AnotherJim · Accepted solution]

Has anyone found a solution to this?  I created a work-around that might work for you, but it requires a little setup.  Basically I added 4 new columns in my data model, 1 for each date component and then 1 column to sort the month logically (rather than alphabetically).  In my situation, PhysicalDate is the date column in the data model table named MPO Receipts:

     Physical Year = YEAR('MPO Receipts'[PhysicalDate])

     Physical Month = FORMAT(DATE(1, MONTH('MPO Receipts'[PhysicalDate]), 1), "MMM")

     Physical Day = DAY('MPO Receipts'[PhysicalDate])

     Physical MonthSort = MONTH('MPO Receipts'[PhysicalDate])
Note: If you want the month name to be spelled out, just format with 4 Ms: "MMMM".

I sorted Physical Month by the column Physical MonthSort. 

I added the 3 date element columns to a slicer in the hierarchy of Year-Month-Day and it seems to work fine for me.  The slicer options are limited to only the dates in my model.  So far it's working as I want it to.


Note: If you also need the quarter date element in your slicer, just add another column to your model and then in the slicer:

    Physical Quarter = "Qtr " & QUARTER('MPO Receipts'[PhysicalDate])

Alternatively, these columns can be created in the source outside of Power BI, if your source allows it.

I'm interested to know if this helps anyone.

[Lierreyy · Accepted solution]

Nice solution @AnotherJim! 

I looked back at this and I think I also found a solution that works. I created a measure that counted the number of rows of data there is in my dataset:

Number of Products = COUNTROWS(Data)

I then added it as a filter on the slicer to show only if it's greater than or equal to 1. Then only the dates that actually appears shows up in the slicer!

[AnotherJim]

Thanks for the compliment, @Lierreyy.

But I think I like your solution better; it's cleaner and much quicker/easier to set up.  Thanks for sharing!

[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! 

[Lierreyy]

Hi @peter_c, 

There's a couple of solutions to this issue now accepted as solutions to this post. They might work for you in future cases where you need this functionality.

[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. 😞

[Lierreyy]

Hi @Spotto, 

There's a couple of solutions to this issue now accepted as solutions to this post. They might work for you in future cases where you need this functionality.