Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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] ) ) ) )
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |