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
divyed's avatar
divyed
Super User
1 month ago

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,