topic Re: WEEKNUM - Every 10th week is 6 digits in DAX Commands and Tips

WEEKNUM - Every 10th week is 6 digits

kjssdca — Thu, 09 Apr 2020 23:13:11 GMT

Created a Dates table that includes a column for Week Number using DAX WEEKNUM function. Most of the data is correct, however for some reason every 10th week is showing up as 6-digit numbers that increment for each day of that week. This is the case for week numbers 10, 20, 30, 40 and 50. In example below, 143527 is Sunday, 143528 is Monday, etc. of week number 10. I tried changing data format for Week column from Text to Whole Number and even deleted and re-created the table, but still having the issue. Does anyone know how to fix this?

Re: WEEKNUM - Every 10th week is 6 digits

camargos88 — Fri, 10 Apr 2020 00:09:47 GMT

Hi kjssdca ,

Try this code:

D = ADDCOLUMNS(CALENDAR(TODAY()-180; TODAY()); "Year"; FORMAT([Date]; "yyyy"); "Month"; FORMAT([Date]; "mmm"); "Week"; WEEKNUM([Date]; 1))

I reproduced your code and I think the weeknum function inside function makes the return wrong. Try this code without format the week.

Ricardo

Re: WEEKNUM - Every 10th week is 6 digits

amitchandak — Fri, 10 Apr 2020 08:59:21 GMT

kjssdca ,

Do not use format in that column. You only need Weeknum([Date],1)

Re: WEEKNUM - Every 10th week is 6 digits

kjssdca — Fri, 10 Apr 2020 20:17:25 GMT

Okay, removing FORMAT worked - Thanks amitchandak !

Re: WEEKNUM - Every 10th week is 6 digits

amitchandak — Sat, 11 Apr 2020 03:38:09 GMT

kjssdca , Please mark the solution