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
martinmark's avatar
martinmark
New Member
1 month ago

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. 

Rupa01's avatar
Rupa01
Solution Sage
1 month ago

martinmark, Since the table is in Direct Query Mode, time intelligence option will not provide you automati Date Hierarchy creation - its a condition stated in the Microsoft Reference - Auto date/time in Power BI Desktop - Power BI | Microsoft Learn

If you need Date hierarchy, you will have to manually create date hierarchy, Reference - How to Create Date Hierarchy in Power BI? [With Examples]

 

 

💡 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
  • martinmark's avatar
    martinmark
    New Member
    1 month ago

    Hi Rupa01 , thanks for your further answer!

    The problem is that none of the methods shown in the article apply to my case.

     

    • Power BI auto create date hierarchy: as we said, this does not work because it is a DirectQuery connection
    • Add date hierarchy Power BI: the "Select the Year column -> click..." method doesn't work because I do not have a Year column. My date column is a single column of timestamps as shown in the my question 
    • the SharePoint List method is basically the same as the first and Date Hierarchy does not get generated automatically in my case.
    • Power BI Add Week to Date Hierarchy: This does not work because I am in DirectQuery mode


    How come is this such of a challenge?

    Thanks

     

    • Rupa01's avatar
      Rupa01
      Solution Sage
      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
      • martinmark's avatar
        martinmark
        New Member
        1 month ago

        Hi Rupa01, this seems to have worked!


         However, the sorting is a bit confusing... What about "Use MonthNum column to sort MonthName and if you want full Month name you can modify the DAX calculation as required." ? How do I do that?