cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
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
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
3 REPLIES 3
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

Community Champion

Detailed reference

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

 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!
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
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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors