Forum Discussion
Calculating Week Start Date from Week Number
Hi there, I have data by week number like this: but I need to add a column that will show me the week end date like this format: The other complication is that the week numbers are ...
Sorry for the back and forth - the code worked but I need it to be for monday start so I changed the code under week Num to 2. but the data isn't correct.
WeekStart =
MINX(
FILTER (
CALENDAR (
DATE ( LEFT ( [ISO Week of ISO Year], 4 ), 1, 1 ),
DATE ( LEFT ( [ISO Week of ISO Year], 4 ), 12, 31 )
),
WEEKNUM ( [Date], 2 ) = VALUE ( RIGHT ( [ISO Week of ISO Year], 2 ) )
),
[Date]
For example the June 2nd date shows up for 24 May and it should show up as June 2
HI jnrezk,
The options parameter of weeknum function will change the week number calculation logic. If you want to keep the regular work schedule(Sunday to Saturday) and Monday as result, you can add a '+1' offset to my formulas to change the results.
WeekStart =
VAR cYW =
MAX ( Table[YearWeekNum] )
RETURN
MINX (
FILTER (
CALENDAR ( DATE ( LEFT ( cYW, 4 ), 1, 1 ), DATE ( LEFT ( cYW, 4 ), 12, 31 ) ),
WEEKNUM ( [Date], 1 ) = VALUE ( RIGHT ( cYW, 2 ) )
),
[Date] + 1
)Regards,
Xiaoxin Sheng
Hi - sorry no need to add the +1 I found a field that automatically counts the weeks as monday to sunday but the weeks are off as indicated in the image in my other response. I tried your new way with the sunday dates and it didnt solve it either.