Forum Discussion

martinmark's avatar
martinmark
New Member
2 months 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
    2 months 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
Rupa01's avatar
Rupa01
Icon for Solution Sage rankSolution Sage
2 months ago

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