Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
duncanwil
Frequent Visitor

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
1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

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

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

3 REPLIES 3
duncanwil
Frequent Visitor

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
Community Champion
Community Champion

Detailed reference

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

 

Screenshot 2021-10-17 111051.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Fowmy
Super User
Super User

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

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.