Convert date format 'YYww' to FirstDateOfWeek

[spehe]

Hello, I have a column with data containing the last two digits of a year and a two digit week number. I would like to add a new column called FirstDateOfWeek where the first date of the specified week.

Example: 2207 should convert to 2022-02-18

I have tried this code that I got from ChatGPT, but it does not work.

Date.From(Date.From(DateTime.FromText("20" & Text.From([YearWeek], "00-00"), "yyyy-MM-dd")) - Duration.From(Duration.FromDays(Date.DayOfWeek(Date.From(DateTime.FromText("20" & Text.From([YearWeek], "00-00"), "yyyy-MM-dd")) - #datetime(2000, 12, 31, 0, 0, 0)) + Number.FromText("00-01")))

[FreemanZ · Accepted solution]

hi @spehe ,

The gpt code is for Power Query and Ibendlin's code is for a calculated column with DAX. You may also try the following:

FirstWeekDate = 
VAR _year = 20&LEFT([date], 2)
VAR _yearstart = WEEKDAY(DATE(_year, 1, 1), 2)
VAR _week = RIGHT([date], 2)
VAR _days = _week*7 - _yearstart +1
RETURN DATE(_year, 1, 1)+_days

it worked like:

about how to add calculated columns:

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-calculated-tables

[lbendlin]

What is a week, in your opinion?

WEEKNUM function (DAX) - DAX | Microsoft Learn

Assuming you want to use the European system Week 07 begins on Feb 14 2022

Here, the column [Test]  has the numerical value 2207

FirstDateOfWeek = 
var y = 2000+ROUNDDOWN([Test]/100,0)
var w = MOD([Test],100)
var z = GENERATESERIES(Date(y,1,1),Date(y,12,31))
var a = ADDCOLUMNS(z,"wn",weeknum([Value],21))
return minx(filter(a,[wn]=w),[Value])

Even if you assumed the week to start on a Friday (system 15)  it would still result in Feb 11, not Feb18.

[spehe]

Hello Ibendlin, thanks for helping me with this. As I am located in Sweden, for us, the week start on Monday, and we use system 21.
Adding your suggested code gives me an error message:

I am totally new to DAX, and I have no idea what it means.