Showing results for 
Search instead for 
Did you mean: 
Helper V
Helper V

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

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:


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? 

Super User
Super User


Just this will work very well

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

Ashish Mathur

View solution in original post

Super User
Super User


Just this will work very well

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

Ashish Mathur

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!

You are welcome.

Ashish Mathur
Super User
Super User

@rodneyc8063 , power Bi create automatic date Hierarchies


That is what see .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


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


1.Creating Financial Calendar - Start on Any ot the 12 Month
2.Any Weekday Week

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
Super User
Super User

@rodneyc8063 - 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.

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Helpful resources

2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors