Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
t-bow
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
AlexisOlson
Super User
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

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
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

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.