How to use auto date/time just for d_calendar

[Gepax]

I disabled the automatic Date/Time in my Power BI report for performance reasons.

However, I need an automatic date hierarchy in order to use "Day" in a table visual.

When I have the automatic hierarchy, I can create the visual as shown in the image below:

But when I disable the function and create the hierarchy manually, the visual behaves like this:

Is there any way to create an automatic hierarchy even without using the option in the settings?

[Gepax · Accepted solution]

None of the answers worked for my case. The manual hierarchy doesn’t work the same way as the automatic one when I put “Day” in the row header.

But I found a solution:

I created a column DAY = DAY([Date]) and left it in the current format.

I created a second DAY_Text = DAY([Date]) formatted as text and sorted by the first one.

In the matrix visual, I added the text column to the row header and it worked the way I wanted.

[MasonMA]

That's weird! it works on my end.

[Gepax]

Try to use it on the matrix visual instead of table visual. Put the manual hierarchy day on lines/rows and test it. 

edit: month/year on columns too. You'll see that the days numbers does not "agreggate" in the same rows for different months.

[MasonMA]

@Gepax 

You may try,

1. Add a Day-of-Month column to your Date table: Day of Month = DAY ( 'Date'[Date] )

2. Use this column in your hierarchy instead of the raw [Date].

3. Mark your 'Dim-Date' table as Date table.

then create Hierarchy manually as below and add your Day of Month column to this Hierarchy

 

[AmiraBedh]

Hello !

Auto Date/Time is all-or-nothing per model. You can’t turn it on just for d_calendar but you can fully replicate the behavior with a proper date table + hierarchy.

You create a seperate date table :

d_calendar =
VAR MinDate = DATE(2018,1,1) 
VAR MaxDate = DATE(2030,12,31) 
RETURN
ADDCOLUMNS(
CALENDAR(MinDate, MaxDate),
"Year", YEAR([Date]),
"Quarter", "Q" & FORMAT([Date], "Q"),
"Month No", MONTH([Date]),
"Month", FORMAT([Date], "MMM"),
"Year-Month", FORMAT([Date], "YYYY-MM"),
"Day Name", FORMAT([Date], "ddd"),
"Day of Week", WEEKDAY([Date], 2)
)

Mark it as a date table then sort text cols like Month byMonth NoandDay Name by Day of Week.

[Gepax]

That's my d_calendar

d_Calendario =

    ADDCOLUMNS(

        CALENDAR(DATE(2021,1,1), TODAY()),

            "Ano", YEAR([Date]),

            "Mês Num", MONTH([Date]),

            "Mês Nome", FORMAT([Date], "mmmm"),

            "Ano Mês", CONCATENATE(YEAR([Date]), FORMAT(MONTH([Date]), "00")),

            "Dia", DAY([Date]),

            "Competência", CONCATENATE(FORMAT([Date], "mmmm/"), YEAR([Date]))

    )
I've already created a manual hierarchy but the visual does not replicate de previous behavior when using auto hierarchy.

[audreygerred]

Hi! Do you have a date dim table added in? If not, add in a calendar, mark it as a date table and create the hierarchy that you need. I love this table available from SQLBI: Reference Date Table in DAX and Power BI - SQLBI

[Gepax]

Already have this set but the manual hierarchy can't set the visual like the first one... every day number is a different row and every month they repeat so I have like 30*12 rows in my table and a just want 1-31 rows like the image

[audreygerred]

It might be how you have create the column for Day. I recommend using the calendar I sent from SQLBI - it is very robust and this way you do not have to manually create it each time, just use theirs.