cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

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

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,

1 ACCEPTED SOLUTION
Super User

Dates are represented internally as integers, so you can skip DATEADD and do this (make sure the resulting column is set as a date data type):

``````FirstDayOfVersionWeek =
VAR semaineNb = RIGHT ( [Version], 2 )
VAR yearNb = LEFT ( [Version], 4 )
VAR Jan1Weekday = WEEKDAY ( DATE ( yearNb, 1, 1 ), 2 )
VAR DayOffset = IF ( Jan1Weekday >= 5, Jan1Weekday - 4, Jan1Weekday + 3 )
VAR FirstThursdayOfYear = DATE ( yearNb, 1, 7 ) - DayOffset
VAR FirstDayOfVersionWeek = FirstThursdayOfYear + 7 * ( semaineNb - 1 ) - 2
RETURN
FirstDayOfVersionWeek``````
2 REPLIES 2
Super User

Dates are represented internally as integers, so you can skip DATEADD and do this (make sure the resulting column is set as a date data type):

``````FirstDayOfVersionWeek =
VAR semaineNb = RIGHT ( [Version], 2 )
VAR yearNb = LEFT ( [Version], 4 )
VAR Jan1Weekday = WEEKDAY ( DATE ( yearNb, 1, 1 ), 2 )
VAR DayOffset = IF ( Jan1Weekday >= 5, Jan1Weekday - 4, Jan1Weekday + 3 )
VAR FirstThursdayOfYear = DATE ( yearNb, 1, 7 ) - DayOffset
VAR FirstDayOfVersionWeek = FirstThursdayOfYear + 7 * ( semaineNb - 1 ) - 2
RETURN
FirstDayOfVersionWeek``````
Regular Visitor

Thank you ! It's working like a charm, and very nice and simple !