Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jaml
Regular Visitor

The date column can't have gaps in dates

Hi guys,

I have an issue with my date table / dimension.

It is a seperate table that i have used many times before. For some reason when i try to mark it as a date table i get the following message:

Datecolumngaps.PNG

Do you know any fix for this - and if so can you help me out?

Thank you

1 ACCEPTED SOLUTION
v-deddai1-msft
Community Support
Community Support

Hi @jaml ,

 

When you specify your own date table, Power BI Desktop performs the following validations of that column and its data, to ensure that the data:

contains unique values

contains no null values

contains contiguous date values (from beginning to end)

if it is a Date/Time data type, it has the same timestamp across each value

 

I suggest you create a  calculated table contain unique and continuous time using CALENDAR function:

 

Table  =  CALENDAR ( DATE ( 2000, 1, 1 ), DATE ( 2020, 12, 31 ) )

 

For more details, please refer to  https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-date-tables

 

Best Regards,

Dedmon Dai

View solution in original post

5 REPLIES 5
v-deddai1-msft
Community Support
Community Support

Hi @jaml ,

 

When you specify your own date table, Power BI Desktop performs the following validations of that column and its data, to ensure that the data:

contains unique values

contains no null values

contains contiguous date values (from beginning to end)

if it is a Date/Time data type, it has the same timestamp across each value

 

I suggest you create a  calculated table contain unique and continuous time using CALENDAR function:

 

Table  =  CALENDAR ( DATE ( 2000, 1, 1 ), DATE ( 2020, 12, 31 ) )

 

For more details, please refer to  https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-date-tables

 

Best Regards,

Dedmon Dai

We have used calendar dimensions in our product for years using the traditional integer column for indexing and relationships.

 

We CAN designate the dimension as a date table in Power Pivot - using a date field, formatted as date.

 

We CAN NOT designate this same dimension table in Power BI - using the same date field, and formatted as date.

 

Each value IS unique

There are NO null values

There ARE contiguous dates with NO gaps.

 

Please advise.

amitchandak
Super User
Super User

@jaml , Seems like there some missing date. Try creating it using a calendar or calendarauto

 

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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

 

I am used to have Gaps in Date Tables like this 

 

1900-01-01 (for invalid Dates, e.g. No Delivery Date if not delivered yet)

...

2015-01-01 (first used Date 

2015-01-02 ...

2021-12-31 (last Date that i want to use)

...

2100-12-31 (for Invalid dates, e.g. Employeed till if still employed)

 

Why i can't mark as Date Table?

I sometimes get this error connecting to a SQL Date table that has been created and is used across many reports without issue. Most of the time when connecting it for the first time it works without issue, sometimes however I get the error that you have stated. As you stated above, all unique values, no nulls and continuous without gaps. 

I have to exit the dialogue box and refresh the report. Then it fixes itself. I believe that there is an issue on the powerbi side and the refresh jogs it into working correctly. 

 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.