Can we make single column Date to have Auto date ?

[admin_xlsior]

Hi guys,

If I turn off the time intelligence - Auto date/time in Options, and then I created a Calendar table by using CALENDARAUTO(), can me me that 1 field Date only to have a date hierarchy automatic ? 

The reason I turn it off is because I have many tables with dates in it so I do not want it all to have hierarchy, but for that specific Calendar Dimension (table), obviously I want.

Thanks

[Fowmy · Accepted solution]

@admin_xlsior 

If you turn off the Auto Date Time then you need to add a Dates Table with the required hierarchy
You can use the following code to create a dates table. Order Date can be replaced with your fact table date column

Dates = 
VAR StartYear = YEAR( MIN(Orders[Order Date]) )
VAR EndYear = YEAR( MAX(Orders[Order Date]) )
VAR DatesColumn = 
    CALENDAR( 
        DATE(StartYear , 1 , 1),
        DATE(EndYear, 12 , 31)
    )
RETURN
ADDCOLUMNS(
    DatesColumn,
    "Month No" , MONTH([Date]),
    "Month Name" , FORMAT( [Date] , "Mmmm" ),
    "Year" , YEAR([Date]),
    "Month Year No" , (YEAR([Date]) * 100) + MONTH([Date]),
    "Month Year" , FORMAT( [Date] , "Mmm yyyy"),
    "Quarter" , QUARTER([Date]),
    "Qtr Name" , FORMAT( [Date] , "\QQ"),
    "Week Day" , WEEKDAY([Date],2),
    "Week" , FORMAT( [Date] , "Dddd" )  
)

[admin_xlsior]

Hi,

Yes, I actually did the same, but by adding it with DAX in front. But Thanks though, I know I just being lazy 😂