Converting Week and Year for Week 53 to Show Week 01 for Next Year

[markefrody]

Hi,

I'm having difficulties in converting week and year for week  53 (Dec 30 and 31, 2018) to be week 01 for 2019. Please note I am using storage mode as DirectQuery.

Currently what I have is Plan Date which contains the dates only.

I used this DAX formula to derive the week number: Week = CONCATENATE("",Right(CONCATENATE("0",WEEKNUM([Plan Date])),2))

I used this DAX formula to derive the year number: Year = CONCATENATE("",Right(CONCATENATE("0",YEAR([Plan Date])),4))

After getting week and year number, I then combine them using DAX formula:

Year/Week = CONCATENATE([Year],CONCATENATE("/",[Week]))

I don't want to use an IF statement wherein "2018/53" will be "2019/01" since I need to manually change it every year. Is there any convenient way to convert year x/week 53?

Appreciate any help you can provide me. 

Thanks and best regards,

Mark

[AnnaSundboel]

Hi there

In my case, when I try the formula:

Weeknum = CONCATENATE("",Right(CONCATENATE("0",IF(WEEKNUM(Sheet1[SampleDate]) > 52, 1, WEEKNUM(Sheet1[SampleDate]))),2))

it says that there are too many arguments in function Concetenate... The max. is 2. 

Can you help me with that?

BR,

Anna

[tarunsingla]

Hi, Could you please share the formula (copy-paste) from your report. There might be an issue with placement of commas (,) in the formula.

[tarunsingla · Accepted solution]

You can change your calculated columns for week and year slightly, and get the desired results without having to change it every year.

Weeknum = CONCATENATE("",Right(CONCATENATE("0",IF(WEEKNUM(Sheet1[SampleDate]) > 52, 1, WEEKNUM(Sheet1[SampleDate]))),2))

Year = CONCATENATE("",Right(CONCATENATE("0", IF(WEEKNUM(Sheet1[SampleDate]) > 52, YEAR([SampleDate]) +1, YEAR(Sheet1[SampleDate]))),4))

[markefrody]

Thanks @tarunsingla! It works!