Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I'm using the following formula to create a Date Table. Everything shows up appropriately, until I add the Day of the week numbering, at which time, I only see Saturdays or Sundays. I've created a new table that has dedicated column formulas for the day of the week entries, and that works without issue. Is there something wrong with adding them all to a single calculated table that would only display a single day?
Calendar =
ADDCOLUMNS (
CALENDAR (DATE(2000,1,1), DATE(2025,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", FORMAT(WEEKDAY ( [Date] ), "00"),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ))
Solved! Go to Solution.
I'm sorry, I guess I do not fully understand your issue then. When I copy paste your code I get the following when I sort:
To me, it seems that I am getting the expected DayOfWeek.
Possibly posting your actual data and your expected outcome could help someone else provide insight.
Proud to be a Super User!
hello @smithD0, my guess is that because you're using FORMAT( ) which returns "A string containing value formatted as defined by format_string." you're getting unintended results.
Are you required to make your Calendar Table in this method, or are you allowed other options?
edit - So I actually didn't see any data when I first copied your DAX until it finally appeared. And then I saw what you described wher e you could only see Sat/Sun. One of the first things I noticed after that was that the column [Date] was unsorted. I sorted ascending and now I see the [DayOfWeek] as I believe you had hoped.
Try sorting your [Date] ascending.
Proud to be a Super User!
If I remove the FORMAT from the WEEKDAY formula, the result ends up being the same. I have verified that I'm sorting via Date Ascending.
Sort the [Date] column. July 2, 9, 16, & 23 were all Sundays in 2000. Where is the 3rd - 8th in the table and what does your DayOfWeek reflect for those?
Proud to be a Super User!
That's the issue, they are removed when I add any of the formulas that calculate out the day of the week. So when I add in the Day of the Week name, short name and date of the week number, I only get Sundays as a result. Without those formulas, I get all of the days in order through CALENDAR().
I'm sorry, I guess I do not fully understand your issue then. When I copy paste your code I get the following when I sort:
To me, it seems that I am getting the expected DayOfWeek.
Possibly posting your actual data and your expected outcome could help someone else provide insight.
Proud to be a Super User!
That did it! I had to sort ascending. Thank you.