topic Re: Convert date format 'YYww' to FirstDateOfWeek in DAX Commands and Tips

Convert date format 'YYww' to FirstDateOfWeek

spehe — Thu, 19 Oct 2023 09:17:23 GMT

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")))

Re: Convert date format 'YYww' to FirstDateOfWeek

lbendlin — Sat, 21 Oct 2023 20:15:28 GMT

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.

Re: Convert date format 'YYww' to FirstDateOfWeek

spehe — Mon, 23 Oct 2023 07:06:26 GMT

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.

Re: Convert date format 'YYww' to FirstDateOfWeek

FreemanZ — Mon, 23 Oct 2023 08:01:16 GMT

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