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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jfernand
Frequent Visitor

Converting date into YYYYWW

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?

1 ACCEPTED SOLUTION
Chihiro
Solution Sage
Solution Sage

I'd do it in this manner.

 

Week Number =
INT (
    CONCATENATE (
        YEAR ( 'Calendar'[Date] ),
        FORMAT ( WEEKNUM ('Calendar'[Date] ), "00" ) 
)
)

View solution in original post

8 REPLIES 8
Chihiro
Solution Sage
Solution Sage

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

@Sean @jfernand  How about 

WeekNum =
 ( YEAR ( Calendar[date] ) * 100 )
    + WEEKNUM ( Calendar[date] )
Konstantinos Ioannou
Anonymous
Not applicable

Let's keep it going! 😄

 

YearMo = YEAR(Calendar(Date)) & RIGHT("0" & WEEK(Calendar(date)), 2)
Sean
Community Champion
Community Champion

@konstantinosIts good to see you back on here Smiley Happy

@ChihiroI should have thought of that shortcut - I like it Smiley Happy

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... Smiley Happy

Week Number =
INT (
    YEAR ( 'Calendar'[Date] )
        & RIGHT ( "0" & WEEKNUM ( 'Calendar'[Date] ), 2 )
)

 @jfernand Take your pick! Smiley Happy

Anonymous
Not applicable

Here is a simplier one

 

Week Number = YEAR(Calendar(date)) & FORMAT(WEEK(Calendar(date)), "00")
Anonymous
Not applicable

=FORMAT(Calendar[Date], "YYYYMM")

Sean
Community Champion
Community Champion

@jfernand

This should work... Smiley Happy

Week Number = 
    INT (
        CONCATENATE (
            YEAR ( 'Calendar'[Date] ),
            CONCATENATE (
                IF ( WEEKNUM ( 'Calendar'[Date] ) < 10, "0", "" ),
                WEEKNUM ( 'Calendar'[Date] )
            )
        )
    )

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.