Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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,
Solved! Go to Solution.
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
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 !
User | Count |
---|---|
85 | |
79 | |
64 | |
52 | |
46 |
User | Count |
---|---|
101 | |
49 | |
42 | |
39 | |
38 |