Date Heirarchy shows in BI, but not in Pivot Table?

[ptmuldoon]

I'm really not sure if this is a Power BI issue, or I just never noticed before with Excel and Power Pivot.

In my pbix file, I have a table with a simple date column that is formatted and recognized as a date with a hierarchy.

If  I drop that date field into a BI table visual, I then also see the Hierarchy

But after I publish this to the service and then connect to that DataSet in Excel, the Pivot Table does not give me the Hierarchy almost as if treats all date's as text?

EDIT.   And interestly, when I look at the datmodel in the Service online, that Date Heirarchy is missing?  yet it exists in the pbix file when viewing on desktop.

[v-cgao-msft · Accepted solution]

Hi @ptmuldoon ,

You might consider creating a calendar table that contains the required hierarchical fields.

Calendar = 
ADDCOLUMNS(
    CALENDAR(MIN('DaliyData'[Date]),MAX('DaliyData'[Date])),
    "Year",YEAR([Date]),
    "Quarter","Qtr " & QUARTER([Date]),
    "Quarter Number",QUARTER([Date]),
    "Month",FORMAT([Date],"mmmm"),
    "Month NUmber",MONTH([Date]),
    "Day",DAY([Date])
)

You can also turn off Auto Date/Time and create Heirarchy manually:

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

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group