AUTOCALENDAR does not automatically let the date column become a hierarchy of year-month-day?

[alicek]

Hello, 

Does anyone know how to make PowerBI recognize a date column automatically as a hierarchy of Day, Month, and Year? It worked in one of my files when it was a simple date column; however, it does not do so in my file that has a separate date table made using AUTOCALENDAR(). 

The reason I ask is that currently, the only measure in the date table that PowerBI will recognize as a "continuous" variable for x-axis (and therefore enable a trend line) is the date column in the date table. But it forces all others, like the Year or Month measures, to be categorical, and doesn't let me apply a trend line. 

Doesn't work here:

 

Does work here:

In my other files, with the date column, it did work.

Under the Modeling tab, their Data Type is both "Date."

Both of them are recognized as Date data types. 

Any thoughts?

Thank you,

Alice

[mwegener · Accepted solution]

Hi @alicek ,

I found the following workaround.

Use binning on date.

https://docs.microsoft.com/en-us/power-bi/desktop-grouping-and-binning#using-binning

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[alicek]

@mwegener Thank you! That worked! It's a work around, but unfortunately it doesn't have quarters as any options, only year, month and day. That's fine for now -- but any idea how to get Quarters in there as an option?

Or any idea why it is not automatcally creating the hierarchy like in the other file?

[mwegener]

Hi @alicek ,

FYI

https://docs.microsoft.com/en-us/power-bi/desktop-auto-date-time

Please give Kudos for support.

[JarroVGIT]

Have you marked it as a datetable?

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[alicek]

Hm @JarroVGIT I'm not seeing that option. Could explain the path to see that button?