cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RonaldvdH Member
Member

Convert date into YEAR-Week

I need to change this formula so that the format will be YYYY-WW

THis formula works but the result is 201801 or 201802 so without the '-' between Year and Week

 

How do i change this formula ?

 

 

Week Number =
INT (
CONCATENATE (
YEAR ( 'Date'[Date] );
CONCATENATE (
IF ( WEEKNUM ( 'Date'[Date] ) < 10; "0"; "" );
WEEKNUM ( 'Date'[Date] )
)
)
)
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Convert date into YEAR-Week

Hi @RonaldvdH 

Create a caluclated column

Column = IF(WEEKNUM([Date])<10,FORMAT([Date],"YYYY-0WW"),FORMAT([Date],"YYYY-WW"))
Capture7.JPG
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Highlighted
Super User III
Super User III

Re: Convert date into YEAR-Week

But it won't be a number of course:

Week Number =
CONCATENATE (
    YEAR ( 'Date'[Date] );
    "-"
        & CONCATENATE (
            IF ( WEEKNUM ( 'Date'[Date] ) < 10; "0"; "" );
            WEEKNUM ( 'Date'[Date] )
        )
)

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  Datanaut

RonaldvdH Member
Member

Re: Convert date into YEAR-Week

@AlB then how do i fix the issue ?

Ive altered the formula but, like you said, it returned an error that it can't convert type Tekst to Number.

 

Week Number =
       INT (
            CONCATENATE (
                      YEAR ( 'Date'[Date] );"-" &
                      CONCATENATE (
                                   IF ( WEEKNUM ( 'Date'[Date] ) < 10; "0"; "" );
                                   WEEKNUM ( 'Date'[Date] )
                                )
                )
)
RonaldvdH Member
Member

Re: Convert date into YEAR-Week

@AlB  any ideas ? a different formula is also ok

Community Support Team
Community Support Team

Re: Convert date into YEAR-Week

Hi @RonaldvdH 

Create a caluclated column

Column = IF(WEEKNUM([Date])<10,FORMAT([Date],"YYYY-0WW"),FORMAT([Date],"YYYY-WW"))
Capture7.JPG
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

RonaldvdH Member
Member

Re: Convert date into YEAR-Week

@v-juanli-msft that did the trick, thank you 🙂 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors