Fiscal Date Hierarchies - Mark as Date Table

[GenoRockMdt]

I have looked high and low... watched the Guy in a Cube videos on the topic and I am left with the following.... 

The magic with date hierarchies in Power BI seems to be that Power BI automatically creates the hidden date hierarchy tables for every date field using the standard calendar.  The hierarchy then appears as you use the date fields and you can switch between relying on the date alone or the hierarchy.

The documentation linked below seems to suggest that we can substitute our own date table that would provide our Fiscal Date hierarchy (automatically?).

https://docs.microsoft.com/en-us/power-bi/desktop-date-tables#setting-your-own-date-table

But then it has this statement:

"It's important to note that when you specify your own date table, Power BI Desktop does not auto-create the hierarchies that it would otherwise build into your model on your behalf."

So how do we get those hierarchies created then?  Do we have to repeat the date table and create fiscal date hierarchies for every date where we want the hierarchy?  I don't see the value then in the "Mark as Date Table" setting if this is the case.

My source tables are all from a SQL server and I do worry that something isn't clicking for me when my source date fields are defined as Date format but the Power Query view shows the dates coming in with DateTime information.  I can set the fields to use Date format but Power BI seems to be doing some interpretation on its own.

Please advise.

Thanks!

[mopetkewi]

I'm having this issue now.  Also finding no solution in any of the videos/help documents.

[jdbuchanan71]

Hello @GenoRockMdt 

You can create that hierarchy yourself on your master date table by dragging a field onto the date field.  For example, I drug Month onto my date field to start a new hierarchy names "Date Hierarchy":

I can add additional fields to it by dragging them from the list and dropping them on the "Date Hierarchy" line and once they are in the hierarchy I can change the order I want them to drill through.  If I was Date > Year > Month I can do that.

You can create multiple hierarchies if you want, maybe you want one that is just Month > Quarter > Year.  You would start it by dragging the Quarter field and dropping it on the Month field and PowerBI would start a new hierarchy. 

You can use this to create hierarchies in other tables as well, customer for example you may want a  Country > Region > Customer hierarchy.

[GenoRockMdt]

Thanks for describing the basic mechanics around creating hierarchies @jdbuchanan71 . My challenge is less around creating date hierarchies in particular but rather around implementing them.  The "Mark as Date Table" function seems to end up with no special functionality.  I would expect that by selecting "Mark as Date Table", Power BI would treat my table and related hierarchy just like the default date hierarchy that is spelled out nicely in Patrick's video here: https://www.youtube.com/watch?v=i8aKjGZd5kY

Several of my data tables have 10-12 date fields and the way that Power BI automatically creates hidden date mappings is ideal - except that I need the underlying date hierarchy to reflect our fiscal calendar.

Thanks!