Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.