Forum Discussion
Solved
Need help creating week number
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 no...
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.
In DAX, you can create calculated those columns :
- [Iso Week] = WEEKNUM([Date];21)
- [Iso Year] = IF( AND(WEEKNUM([Date];21) < 5;WEEKNUM([Date];2) > 50);[Year]+1;IF(AND(WEEKNUM([Date];21) > 50;WEEKNUM([Date];2) < 5);[Year]-1;[Year]))
- [Iso Year Week] = IF( AND(WEEKNUM([Date];21) < 5;WEEKNUM([Date];2) > 50);[Year]+1;IF(AND(WEEKNUM([Date];21) > 50;WEEKNUM([Date];2) < 5);[Year]-1;[Year])) & " week " & FORMAT(WEEKNUM([Date];21);"00")
Very helpful, thanks!