How to reproduce the features of the auto hierarchy date

[ladpedro]

Hi!

I need to disable the option of auto generate date tables for time intelligence to free up espace in memory. But I’ve charts with the auto hierarchy date that I couldn’t reproduce its features manually.

When using Year, Month and Day of the auto hierarchy, starting with the Year, I can drill-down to see data agreggate by each month, but I also can expand the Year level to see Month per Year analysis, where the axis x contains month’s name in vertical and year in horizontal (image).

I don’t be able to do it without auto hierarchy.

I've tried to:

- make a manually hierarchy with Year, Month and Day, but the result is different. It shows month like numbers. If I put the month’s name instead of month, I can’t disable the concatenation of month and year.
- delete auto hierarchy on each table it was generated that I won’t use the hierarchy, but it isn’t possible.
- grouping data in bins of months to reproduce, but I couldn’t format it in the same way like in the image.

So, how to reproduce manually the same features of the automatic date hierarchy?

I appreciate any help.

Thanks.

[johnt75 · Accepted solution]

It is annoyingly difficult to replicate the behviour of the auto date hiearchies, but the SQLBI guys have some good articles about it. You could start with https://www.sqlbi.com/articles/creating-a-simpler-and-chart-friendly-date-table-in-power-bi/ and they link to other articles within that, those are worth reading too.

[ladpedro]

Thanks @johnt75 !

At the end of this article had exactly what I needed. I did reproduce auto hierarchy date. So I'll transcribe here to facilitate colleagues with the same issue to find the solution.

Reference: https://www.sqlbi.com/articles/creating-a-simpler-and-chart-friendly-date-table-in-power-bi/

Author: Marco Russo

Auto date/time behavior

Another workaround to use the continuous axis is by using the same metadata used by the Auto date/time feature in Power BI. Automatic date table show a single hierarchy that uses a continuous axis even though the columns quarter and month are categorical. This can be obtained by using particular attributes of the date table:

• Date Table – property DataCategory: Time (this is automatically set by Mark as Date Table)
• Date Column – property DataCategory: PaddedDateTableDates (must be set with Tabular Editor)
• Year Column – property DataCategory: Years (must be set with Tabular Editor)

Moreover, the navigation must be done using a hierarchy: just placing individual attributes in the axis does not enable the Continuous axis type for the charts in Power BI.