The date column can't have gaps in dates

[jaml]

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:



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

Thank you

[v-deddai1-msft · Accepted solution]

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

[CLEARIFY]

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]

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

[ProdwareSaM]

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?

[DataLicious]

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.