Forum Discussion
Change week & year format
Hello everyone,
I have a column with date and created costum column to get week and year based on that date.
OrderWeekYear = IF(WEEKNUM(WorkOrder[Order Date)<10,FORMAT([Order Date],"YYYY-0WW"),FORMAT([Order Date],"YYYY-WW"))Result shows that 1st january of 2023 is 1st week of 2023 and it is sunday.
Now i am trying to find a way to modify this code to get needed result.
Correct answer should be 1st january of 2023 is 52nd week of 2022.
Already tried this but nothing happen.
OrderWeekYear = IF(WEEKNUM(WorkOrder[Order Date],21)<10,FORMAT([Order Date],"YYYY-0WW"),FORMAT([Order Date],"YYYY-WW"))Regarding to graphic, in 2022 we have only 52 weeks. But it shows that we have 53 which is not correct.
Thank you.
Hi GadeshevArman ,
In the WEEKNUM() function, if there is no return type mentioned by default the week containing 1st Jan is considered as week 1.
You can refer to this -
https://learn.microsoft.com/en-us/dax/weeknum-function-dax
This will give you desired result -
OrderWeekYear =var _week = WEEKNUM('Date Table'[Date],21)returnIF(_week<10,year('Date Table'[Date])&"-0"&'Date Table'[Column],YEAR('Date Table'[Date])&"-"&'Date Table'[Column])
2 Replies
Hi GadeshevArman ,
In the WEEKNUM() function, if there is no return type mentioned by default the week containing 1st Jan is considered as week 1.
You can refer to this -
https://learn.microsoft.com/en-us/dax/weeknum-function-dax
This will give you desired result -
OrderWeekYear =var _week = WEEKNUM('Date Table'[Date],21)returnIF(_week<10,year('Date Table'[Date])&"-0"&'Date Table'[Column],YEAR('Date Table'[Date])&"-"&'Date Table'[Column])GadeshevArman , Based on Monday or Sunday week you can have 52 or 53 week
You can get like
Week Num = Year([Date]) & format(weeknum([Date],2), "00") //monday week
sometime we merge week 53 into 52
Week Num = Year([Date]) & format(if( weeknum([Date],2)>52, 52, weeknum([Date],2 )) , "00") //monday week
