Forum Discussion

martinmark's avatar
martinmark
New Member
1 month ago
Solved

Date Hierarchy does not get generated

my Date field named created_at is a Date/Time in Power Query when I load the data but not in the model (Data view). Why? How do I make the date hierarchy to get generated automatically?   ...
Need Help
  • Rupa01's avatar
    Rupa01
    1 month ago

    martinmark  You will have to first create the columns in the table which are required for creating the Date Hierarchy - possible in Direct Query Mode by using DAX. Then, you can use those columns to create a hierarchy as required.

    Note - Use MonthNum column to sort MonthName and if you want full Month name you can modify the DAX calculation as required.

     

    Create below DAX Calculated columns in the table in which you have created_at column - 

    Year = YEAR('Table'[created_at])

Quarter = "Q" & QUARTER('Table'[created_at])

MonthNum = MONTH('Table'[created_at])

MonthName = 
SWITCH(
    MONTH('Table'[created_at]),
    1, "Jan",
    2, "Feb",
    3, "Mar",
    4, "Apr",
    5, "May",
    6, "Jun",
    7, "Jul",
    8, "Aug",
    9, "Sep",
    10, "Oct",
    11, "Nov",
    12, "Dec"
)

     

    💡 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
Prince0011's avatar
Prince0011
Solution Sage
1 month ago

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:

💡 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