Forum Discussion

duncanwil's avatar
duncanwil
Frequent Visitor
4 years ago
Solved

Calendar Returning Unusual Week Numbers

I have created a date table using DAX ...  Date = CALENDAR("01 Jan 2020","31 Dec 2021") I then created two columns: Month Number and Week Number:  Month Numberb = FORMAT('Date'[Date],"YYmm")...
Need Help
  • Fowmy's avatar
    Fowmy
    4 years ago

    duncanwil 

    You can use the right function (WEEKNUM) to extract the week number than the FORMAT function. Use the following calendar table code. it should work for you. 

    Dates =
VAR StartYear = 2020
VAR EndYear = 2021
VAR DatesColumn =
   CALENDAR(
       DATE(StartYear , 1 , 1),
       DATE(EndYear, 12 , 31)
   )
RETURN
ADDCOLUMNS(
   DatesColumn,
   "Month No" , MONTH([Date]),
   "Month Name" , FORMAT( [Date] , "Mmmm" ),
   "Year" , YEAR([Date]),
   "Month Year No" , (YEAR([Date]) * 100) + MONTH([Date]),
   "Month Year" , FORMAT( [Date] , "Mmm yyyy"),
   "Quarter" , QUARTER([Date]),
   "Qtr Name" , FORMAT( [Date] , "\QQ"),
   "Week Day" , WEEKDAY([Date],2),
   "Week" , FORMAT( [Date] , "Dddd" ),
   "Week No" , WEEKNUM([Date],2),
   "Week Num" , "WK - " & WEEKNUM([Date],2) 
)

     

Fowmy's avatar
Fowmy
Super User
4 years ago

duncanwil 

You can use the right function (WEEKNUM) to extract the week number than the FORMAT function. Use the following calendar table code. it should work for you. 

Dates =
VAR StartYear = 2020
VAR EndYear = 2021
VAR DatesColumn =
   CALENDAR(
       DATE(StartYear , 1 , 1),
       DATE(EndYear, 12 , 31)
   )
RETURN
ADDCOLUMNS(
   DatesColumn,
   "Month No" , MONTH([Date]),
   "Month Name" , FORMAT( [Date] , "Mmmm" ),
   "Year" , YEAR([Date]),
   "Month Year No" , (YEAR([Date]) * 100) + MONTH([Date]),
   "Month Year" , FORMAT( [Date] , "Mmm yyyy"),
   "Quarter" , QUARTER([Date]),
   "Qtr Name" , FORMAT( [Date] , "\QQ"),
   "Week Day" , WEEKDAY([Date],2),
   "Week" , FORMAT( [Date] , "Dddd" ),
   "Week No" , WEEKNUM([Date],2),
   "Week Num" , "WK - " & WEEKNUM([Date],2) 
)