Adding Day of the Week value to Calendar Table formula only shows Saturday or Sunday values

[smithD0]

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" ))

[ChrisMendoza]

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.

[smithD0]

@ChrisMendoza

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.

[ChrisMendoza]

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?

[smithD0]

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

[ChrisMendoza · Accepted solution]

@smithD0,

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.

[smithD0]

That did it!  I had to sort ascending.  Thank you.