Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi team,
I have a date in the 2017-01-01 format. I wanted to convert it into 201701 (week number 1 of the year 2017)
I have used the formula
Week Number = YEAR( Calendar(date) ) & WEEK( Calendar(date) )
and the result I get is 20171
I need it to be exactly 201701. How should I procceed?
Solved! Go to Solution.
I'd do it in this manner.
Week Number = INT ( CONCATENATE ( YEAR ( 'Calendar'[Date] ), FORMAT ( WEEKNUM ('Calendar'[Date] ), "00" )
)
)
I'd do it in this manner.
Week Number = INT ( CONCATENATE ( YEAR ( 'Calendar'[Date] ), FORMAT ( WEEKNUM ('Calendar'[Date] ), "00" )
)
)
wow, apparently there were tons of options for this scenario. Thank you everyone for your help. This option ended up being the most complete and yet simple
Let's keep it going! 😄
YearMo = YEAR(Calendar(Date)) & RIGHT("0" & WEEK(Calendar(date)), 2)
@konstantinosIts good to see you back on here
@ChihiroI should have thought of that shortcut - I like it
I actually use something similar for a Month Order Column
Month Order = VALUE ( FORMAT ( 'Calendar'[Date], "YYYYMM" ) )
@AnonymousI believe you were in a hurry to post...
Week Number = INT ( YEAR ( 'Calendar'[Date] ) & RIGHT ( "0" & WEEKNUM ( 'Calendar'[Date] ), 2 ) )
@jfernand Take your pick!
Here is a simplier one
Week Number = YEAR(Calendar(date)) & FORMAT(WEEK(Calendar(date)), "00")
=FORMAT(Calendar[Date], "YYYYMM")
This should work...
Week Number = INT ( CONCATENATE ( YEAR ( 'Calendar'[Date] ), CONCATENATE ( IF ( WEEKNUM ( 'Calendar'[Date] ) < 10, "0", "" ), WEEKNUM ( 'Calendar'[Date] ) ) ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
146 | |
110 | |
107 | |
86 | |
63 |