Add Week Number and Week Number and Year to Date Table

[Anonymous]

I am currently using the following to create a Date Dim.

Date =
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" ),
"MonthShortYear",FORMAT([Date], "mmm-YYYY"),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)

I would like to also have the following in the table:

• Week Number
• Week Number and Year

Please let me know if you have suggestion how to include this.

Thanks.!

[Daniil · Accepted solution]

Here's what you can try:

Date = 
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" ),
"MonthShortYear",FORMAT([Date], "mmm-YYYY"),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ),
"Week Number", WEEKNUM ( [Date] ),
"Week Number and Year", "W" & WEEKNUM ( [Date] ) & " " & YEAR ( [Date] ),
"WeekYearNumber", YEAR ( [Date] ) & 100 + WEEKNUM ( [Date] )
)

'Date'[WeekYearNumber] is used to sort 'Date'[Week Number and Year].

Also, this article might be useful to you: https://www.sqlbi.com/articles/using-generate-and-row-instead-of-addcolumns-in-dax/