March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
I have a project that I am working on with a simplified calendar used as a DimDate. This calendar has: Date, Year, Month, Quarter, Month Name, Day of Week, Week Number, and YearMonth. What it does not have is ISO Week number. The week number that it has is calculated using =WEEKNUM,[date],2 and those numbers that do not match for sales amounts. Is there a simple way to create an ISO week number so that I can use this project for the next 10 days until my calendar frees up for me to rework the entire thing?
Is it =WEEKNUM, [Date], 21?
Proud to be a Super User!
Solved! Go to Solution.
yes. I started out with serial number 2 as that is what I was told the program used. It turns out that our Navision uses ISO. I had created week numbers using serial number 2 but was able to correct the informaiton chaning it wo week serial number 21.
Happy to know I was not the only one using 2.
Proud to be a Super User!
Hi Team,
Need some help here.
The default WEEKNUM function provides week number from the start of the year and runs 1 to 53 or 54.
If I need to show week number within the quarter, running 1 to 13 or 14 within each qtr, how should I do so?
I.e.
Thanks!
Is there a solution for this problem? I am also encountering this issue.
The problem is that I don't need the week of the year, but the iso-week for my reports. This currently doesn't seem te be an option in Power BI.
My understanding of the ISO week number system is that it begins in Monday and ends on Sunday, days 1-7. Do you want just the ISO week number? That would be:
= WEEKNUM([Date],2)
If you want the full ISO date as specified here: https://en.wikipedia.org/wiki/ISO_week_date
That would be (compact form):
= YEAR([Date]) & "W" & WEEKNUM([Date],2) & WEEKDAY([Date],2)
See WEEKNUM reference here: https://support.office.com/en-US/article/WEEKNUM-Function-DAX-e636ef36-180a-4e2d-a29b-8f549c258da0
Very helpful, thanks!
Wild, learned something, I guess the documentation on WEEKNUM is incomplete, I couldn't find 21 as a valid argument anywhere for WEEKNUM but tried it and it works, and it does make a difference.
https://support.office.com/en-US/article/WEEKNUM-Function-DAX-e636ef36-180a-4e2d-a29b-8f549c258da0
But, found confirmation from Brueckl
http://blog.gbrueckl.at/2012/04/iso-8601-week-in-dax/
yes. I started out with serial number 2 as that is what I was told the program used. It turns out that our Navision uses ISO. I had created week numbers using serial number 2 but was able to correct the informaiton chaning it wo week serial number 21.
Happy to know I was not the only one using 2.
Proud to be a Super User!
I added WEEKNUM([createdAt],2) but kept on getting error - "Expression error: The name 'WEEKNUM' wasn't recognized. Make sure it's spelled correctly"
I am connecting to mysql database ..
Try this:
Date.WeekOfYear(#date)
#date = The column that contains your date.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
94 | |
86 | |
84 | |
76 | |
49 |
User | Count |
---|---|
160 | |
144 | |
103 | |
74 | |
57 |