DAX - how to compute 4 steps of calculation requiring 4 columns in only one DAX formula / resulting in one column

Question

I have the 4 following steps of calculation with DAX in Power BI : assuming that I have ISO week numbers in the following format : YYYY-WW in a column whose name is [Version], I'm calculatnig the first day of each week with these 4 steps, requiring 4 intermediate columns :

step 1 :

FirstSeventhDayOfYear = 
VAR semaineNb = RIGHT([Version], 2) //get the week number
VAR yearNb = LEFT([Version], 4) // get the year
VAR FirstSeventhDayOfYear_ = DATE(yearNb, 1, 7) //get the Jan 7th of this year
Return FirstSeventhDayOfYear_

step 2 in a new column :

FirstThursdayOfYear = 
VAR yearNb = LEFT([Version], 4)
VAR FirstThursdayOfYear_ = IF( WEEKDAY( DATE( yearNb, 1, 1 ), 2)>=5, DATEADD( 'Table'[FirstSeventhDayOfYear].[Date] , -(WEEKDAY( DATE( yearNb, 1, 1 ), 2)-4), DAY ), DATEADD( 'Table'[FirstSeventhDayOfYear].[Date] , -(WEEKDAY( DATE( yearNb, 1, 1 ), 2)+3) , DAY))
Return FirstThursdayOfYear_

step 3 in another new column :

ThursdayForSemaine = 
VAR semaineNb = RIGHT([Version], 2)
VAR yearNb = LEFT([Version], 4)
VAR ThursdayForSemaine_ = DATEADD('Table'[FirstThursdayOfYear].[Date], 7*(semaineNb-1), DAY)
Return ThursdayForSemaine_

step 4, last step :

FirstDayOfVersionWeek = 
DATEADD( 'Table'[ThursdayForSemaine].[Date] , -2, DAY) 

I would like to have all this done in only one DAX formula, and one column with no intermediate columns, but I cannot manage it, because the DATEADD function is complaining (when I store the result of intermediate calculations in one variable) that the variable is not a table. Encapsulating the result in {} to get a list do not work either...The message is that DATEADD function is not able to deal with columns created with ADDCOLUMNS or SUMMARIZE

Is there a solution to this question ?

Thanks,

Answer 1

Instead of using DATEADD(x, y, DAY) just use add/subtract the number of days from a Date eg: DATE(2023, 1, 1) - 1 will give you DATE(2022, 12, 31).

So your final formula could look like (not tested):

FirstDayOfVersionWeek = 
VAR semaineNb = VALUE( RIGHT([Version], 2) ) //get the week number
VAR yearNb = VALUE( LEFT([Version], 4) ) // get the year
VAR FirstSeventhDayOfYear = DATE(yearNb, 1, 7) //get the Jan 7th of this year
VAR FirstThursdayOfYear = 
  IF(
    WEEKDAY( DATE( yearNb, 1, 1 ), 2) >=5,
    FirstSeventhDayOfYear - (WEEKDAY( DATE( yearNb, 1, 1 ), 2) - 4),
    FirstSeventhDayOfYear - (WEEKDAY( DATE( yearNb, 1, 1 ), 2) + 3)
  )
VAR ThursdayForSemaine = FirstThursdayOfYear + 7*(semaineNb - 1)
VAR FirstDayOfVersionWeek_ = ThursdayForSemaine - 2

RETURN FirstDayOfVersionWeek_