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 we...
Date Conversion
  • FreemanZ's avatar
    FreemanZ
    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

     

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.