Getting a date from a week number

[TedM]

I'm trying to figure out how to get the date of the first working day from a given year and week number (i.e. 2020 week 43) - any thoughts? 

[Aurelien]

Hi @TedM 

Hard way here...

Create a table DATE with every dates of calendar on one column named "date".

Add a virtual column "_isoweeknum" with expression: 

ISOWEEKNUM([date])

 Add another virtual column "_year" with expression:

YEAR([date])

Then, you can use the expression:

INDEX(
  ORDERBY(
    FILTER("DATE",
      AND(
        [_year]=YEAR([_thisrow].[yourYearSelectionColumn]),
        [_isoweeknum]=ISOWEEKNUM([_thisrow].[yourWeekNumSelectionColumn]),
        WEEKDAY([date])=1
      )
    ),
    [date],
    FALSE
  ),
1)

EDIT:minor correction to expression

[TedM]

Hi @Aurelien

Thanks for the "hard way"!

I've now found a way to achieve what I needed just using the week numbers and not the date.

[Aurelien]

Would you share your method?

[TedM]

I ended up not setting the date at all as I could achieve what I wanted
(finding out whether the week number was more than 12 months ago) just
using the week number.

[Marc_Dillon]

Maybe something along the lines of:

"1/1/" & YEAR(date) + WEEKNUM( date ) * 7 - {offset}

Where offset would be defined as something like:

WEEKDAY( "1/1/" & YEAR( date ) )