cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rodneyc8063
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: 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? 

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

Just this will work very well

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

Just this will work very well

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

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

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!
Greg_Deckler
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

Announcements
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