Calendar Returning Unusual Week Numbers

[duncanwil]

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

Week Numberb = FORMAT('Date'[Date],"YYYY-ww")

It works and I successfully created a relationship so that my Date Table can work with my data and it all seems to work.

The final part of this story is that when I use the Week Numberb relationship, I get all of the week numbers as expected BUT I also get unexpected ones

Expected examples:

2020-10 ... two digit week number

2020-49

2021-23

Unexpected examples:

2020-1 ... one digit week number not two

2020-9

2021-1

And these unexpected results are giving very dfferent results from the expected output.

Example,

expected results 2020-53 = 81.51 ... 2021-10 = 97.01

unexpected results 2021-1 = 12.67 ... 2020-9 = 9.86

My questions are:

where are these week numbers 2021-1, 2020-9 ... coming from?

why are they giving such wild results ... I appreciate you don' know the data but maybe something logical shows the results I have been getting?

TIA

Duncan

[duncanwil]

Thank you both. I have to confess that I had read those pages/suggestions but my lack of DAX experience led me to assume FORMAT was OK.

So, I repgrammed to use WEEKNUM and so on and the tables are behaving as expected.

Duncan

[CNENFRNL]

Detailed reference

 https://docs.microsoft.com/en-us/dax/format-function-dax 

[Fowmy · Accepted solution]

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