Forum Discussion
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")
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
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) )
3 Replies
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) )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
