This content has been marked as final. Show 6 replies
I am really struggling with the weeks in the month, it seems to work for the most part, then fails miserably in January 16.
this is what is currently in situ:
|if((week(Date) - week(monthstart(Date)) + 1)<1,1,(week(Date) - week(monthstart(Date)) + 1)) as WeekNumberInMonth,|
I later found its because the Start of week month is remaining at 53 for the whole of january 2016 because 01/01/16 is week 53 so the maths goes wrong.
I've found out I can fix this by saying :
If (week(monthstart(Date)) >= week(Date)
This is the scenario:
|Week Start||WeekOfYear||WeekNoMonthStart||Expected Result|
|1st January 16||53||53||1|
|4th January 16||1||53||2|
|11th January 16||2||53||3|
Then what i would like to do is based on the current configuration
If the above matches then use the biggest number, replace any instances by 0 and +1
0+0+1 = 1
1+0+1 = 2
2+0+1 = 3
However, I'm not sure how you'd do this?
Many thanks for any assistance.