2 of 2 people found this helpful
So the problem here is that your [Week Number] is the datepart of week...which is just the week number (say 44, or Week 44). When you then try and use this to create a date it only has the number 42! Dates are stored as numbers, which are then converted to the dates we (as humans) understand...starting from 01/01/1900....so 1st Jan 1900 is the number 1, 2nd Jan the number 2...and so on.
So if your [Week Number] was 44...the calculation you have says; convert the number 44 to a date...which would be 13/02/1900 (day 44 from 1900), and then add one to it (to give the 14/02/1900).
would give you a date from the previous week....you may need to substitute the -1, with a calculation on DATEPART('weekday',today()). This returns the weekday number (so Monday = 2, Tuesday = 3....) meaning you can adjust which date is returned depending on which day of the week it is (if for example, you always want to show the date as a weekending value).
Hope that helps and makes sense.
Thanks a lot!
This works great