Forum Discussion

kcantor's avatar
kcantor
Community Champion
10 years ago
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...
Need Help
  • kcantor's avatar
    kcantor
    10 years ago

    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.

     

fbrossard's avatar
fbrossard
Kudo Commander
10 years ago
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")
See http://fbro.wordpress.com/2013/02/07/powerpivot-excel2013-table-temps-universelle-pour-vos-pocs/