Forum Discussion

spehe's avatar
spehe
Helper I
2 years ago
Solved

Convert date format 'YYww' to FirstDateOfWeek

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

 

 

Date Conversion

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    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's avatar
      spehe
      Helper I
      2 years ago

      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.

       

  • FreemanZ's avatar
    FreemanZ
    Super User
    2 years ago

    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