Date Hierarchy does not get generated

Hello martinmark ,

Sometimes you may see the difference ( when auto detect is off) , try below steps to force the model to recognize it as a Date/Time :

1. In Power BI Desktop, go to the Data View 

2. Click on your created_at column.

3. Look at the top ribbon under column tools.

4. Change Data Type to Date/Time (or Date if you don't need the timestamp).

5. Change Format to your preferred date format

I hope this helps,

Did I answer your query ? Mark this as solution if this helps, Kudos are appreciated.

Cheers,

Hi martinmark.,

Date Hierarchy is automatically created in Power BI when Time Intelligence option is enabled in your current PBI Desktop File - File > Options & Settings > Options > Current File > Data Load > Time Intelligence (enable)

Note that this will only work when the table storage mode is 'Import', column data type is Data or DateTime and the column is not in the many side of the relationship.

Microsoft Reference - Auto date/time in Power BI Desktop - Power BI | Microsoft Learn

💡 Helpful? Give a Kudos 👍 — keep the community growing
✅ Solved your issue? Mark as Solution ✔️ — help others find it faster

Best regards,
Rupasree Achari | BI & Fabric Analytics Engineer 

Hi all, thanks for your answers!

divyed , this is exactly my issue. Data Type is set automatically to Date/Time

Rupa01, Time Intelligence option is enabled.

The thing is I am actually using DirectQuery but the data size does not allow me to import. 

The reason your Date Hierarchy is not being generated automatically is usually because Power BI is no longer recognizing created_at as a true Date/Time column in the semantic model, even though it was a Date/Time type in Power Query.

Power BI only creates the automatic hierarchy when:

• The column data type in the Model view/Data view is Date or Date/Time.

• The Auto date/time feature is enabled.

• The column is not being treated as Text or another data type after loading.

You can check the following:

1. Verify the data type in the model

Go to:

Data view → Select created_at column → Column tools

Check:

• Data type = Date/Time

• Format = your preferred date format

If it shows Text, change it to Date/Time.

2. Check Auto Date/Time setting

Go to:

File → Options and settings → Options → Current File → Data Load

Enable:

✅ Auto date/time

Then refresh the model.

Note: This setting must be enabled before Power BI creates the hidden date tables.

3. Check if you are using a DirectQuery or composite model

Automatic date hierarchies are not always available depending on the storage mode and model design.

For enterprise models, the recommended approach is usually not to rely on Auto Date/Time.

4. Recommended approach: Create a Calendar table

For production reports, create a proper Date table:

Calendar =
CALENDAR(
    MIN('Table'[created_at]),
    MAX('Table'[created_at])
)

Add columns:

Year = YEAR('Calendar'[Date])

Month = FORMAT('Calendar'[Date], "MMM")

Month Number = MONTH('Calendar'[Date])

Day = DAY('Calendar'[Date])

Then:

• Mark it as Date table

• Create a relationship:

Calendar[Date] → Table[created_at]

• Use the Calendar fields in visuals.

This gives better control for:

• Time intelligence

• Fiscal calendars

• Sorting months correctly

• Year-over-year analysis

So, if this is a small report, enabling Auto Date/Time may solve it. For a production Power BI model, a dedicated Calendar table is the recommended solution.

For more information:

• Auto date/time in Power BI: https://learn.microsoft.com/power-bi/transform-model/desktop-auto-date-time

• Create and use date tables in Power BI: https://learn.microsoft.com/power-bi/transform-model/desktop-date-tables

💡 Helpful? Give a Kudos 👍 — keep the community growing.

✅ Solved your issue? Mark this as the Accepted Solution ✔️

Best regards, Prince Singh | Data Science & Microsoft Fabric Enthusiast

martinmark 

you can create a new column

date2 = DATEVALUE('Table'[date])

 

then you can see the hierachy for the new column

 

pls  see the attachment below