DAX Calendar() Function "Mystery" .[Day] column?

[rodneyc8063_1]

Im just starting out in PBI and am going through a tutorial right now. 

Specifically I am looking at the training from the Microsoft webpage here: https://docs.microsoft.com/en-us/learn/modules/design-model-power-bi/3-date-table/

Its basically walking you through how to create a Calendar table.

I was able to follow along and understand things until it came to the section talking about how to create a table using DAX.

It says to create a new table and use this formula

Dates = CALENDAR(DATE(2011, 5, 31), DATE(2021, 5, 31))

Makes sense, creates a new table with the CALENDAR() function starting from 2011 to 2021.

It then goes on to say to create additional columns for the month, day, week etc.

Now for the day of the week it says to use the following formula

DayoftheWeek = FORMAT(Dates[Date].[Day], "DDDD")

So this formula basically says to me - Create a column called "DayoftheWeek" using the function FORMAT and with the format of "DDDD" using the Dates[Date].[Day]

Dates is the original table I created, and [Date] is the original column as well. Im confused where the ".[Day]' part of the formula comes from?

When I was typing out the formula, I do see the option to choose Dates[Date].[Day]

So I guess my question is where did this mystery ".[Day]" component come from? 

[Ashish_Mathur · Accepted solution]

Hi,

Just this will work very well

DayoftheWeek = FORMAT(Dates[Date],"DDDD")

[rodneyc8063_1]

This was what I was expecting as well - Why not just use Dates[Date], and why would there be an extra Dates[Date].[Day]

Good to know that your suggested code would work as well, as that was what I was thinking/expecting myself!

And thanks to everyone else as well, I was flabbergasted and not sure where the mystery .[Day] portion came - But I guess its from the auto generated time hierarchy as folks have mentioned.

Many thanks - I thought I was going nuts!

[Ashish_Mathur]

You are welcome.

[amitchandak]

@rodneyc8063_1 , power Bi create automatic date Hierarchies

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

That is what see with.date .year.month etc.

Prefer not to use that on a key column of the date table as one mark it as a date table it will become id and you can not use it

Should we use it or not

https://www.youtube.com/watch?v=v8Srh5xYXv8

How to create date table, FY and Week of your choice

https://youtu.be/Qt0TM-4H09U

1.Creating Financial Calendar - Start on Any ot the 12 Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...
2.Any Weekday Week
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...

[Greg_Deckler]

@rodneyc8063_1 - If you have the time intelligence setting turned on (it is turned on by default) then Power BI automatically creates a hiearchy for any date field that includes year, quarter, month and day and you reference these with the .[] notation.