Reply
avatar user
Anonymous
Not applicable

Data Does Not Contain Certain Dates Resulting in "Blank" in Slicer

Hi,

 

I have multiple tables that I want to connect so I could do yearly comparison analysis across. So I created a Date table as the connector using this formula: 

 

Dates = Calendarauto()
 
But when I want to slice them, some tables where I have created connection with the Date Table are not affected by the filter. When investigated I found this:
 
Inglasses_0-1647232189813.png 
 
Some dates does not exist in my data which exist in Date Table. So my slicer contains "(Blank)"
 
Inglasses_2-1647232634264.png

 

I have checked date format and tables data. They are clear. What should I do to be able to filter those affected tables and get rid of the blank? 

3 REPLIES 3
amitchandak
Super User
Super User

@Anonymous , Make sure you create a calendar with all dates

 

You can use calendar

 

Calendar(date(2018,01,01), date(year(today()),12,31) )

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
avatar user
Anonymous
Not applicable

Hi,

 

thank you for responding. I still face the same issue.

 

Inglasses_0-1647241280577.png

 

The first column that you see as "Due Date" came from one of the data table while "Year", "Quater", "Month" and "Day" comes from the Date Table that I have constructed (No issue on this). 

 

Issue is that, my data has 'Due date' indicated but it is not categorized under the calendar date but instead lump into "Blank" filter.

 

Could it be because it is duplicate date?

 

@Anonymous , If you dues date us joined with date table(Hope it does not have timestamp)

 

1. If due date has timestamp create a only date field to join with date table Datevalue([Due Date])

2. If due date has null/blank value, system will dhow that

3. if calendar has less dates, then when plot due date and date from calendar , calendar date will show blank

 

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-bd52912a5bd4
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.

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)