Convert date into YEAR-Week

[RonaldvdH]

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] )

)

)

)

[MarlenePirker]

Hello! So i tried to use the proposed formula but for 1st of Jan 2023 it shows me 2023.52 

Can somebody help me with this? 

Thank you! 

Best regards, 

Marlene

[v-juanli-msft · Accepted solution]

Hi @RonaldvdH 

Create a caluclated column

Column = IF(WEEKNUM([Date])<10,FORMAT([Date],"YYYY-0WW"),FORMAT([Date],"YYYY-WW"))

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.

[Anonymous]

@v-jutoma  Is there a way to make the above a date so that it can be formatted as a custom date in the modeling area?   Then you can make it continuous on the X axis rather than categorical.   

[saviola07]

Hi Maggie,

I used your method and I thought it worked as a charm, but for some reason it uses the American week (so starts on Sunday).

I tried to amend the formula to this: 

IF(WEEKNUM('Calendar'[Date];2)<10;FORMAT([Date];"YYYY-0WW");FORMAT('Calendar'[Date];"YYYY-WW"))

But I somehow needs to change the format of the "YYYY-WW" but absolutely no idea on how to do that!?

[GadeshevArman]

Hi @saviola07, @v-juanli-msft ,

Is there any solution for this issue? Created column with this:

OrderWeekYear = IF(WEEKNUM([Order Date],21)<10,FORMAT([Order Date],"YYYY-0WW"),FORMAT([Order Date],"YYYY-WW"))

Same result needed, graphic shows that 1st January of 2023 is 1st week of 2023.

Corect result should be 52nd week of 2022.

Thanks in advance.

[Schwurblini]

Hey,

I created something like this: 

Weeks = WEEKNUM([Date],21) & "." & FORMAT([Date], "YYYY")

Output:

52.2022

52.2022

1.2023

1.2023

and so on, it will initially be a text, but can be formated to a number

Does this help?

[RonaldvdH]

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

[AlB]

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  

[RonaldvdH]

@AlB  any ideas ? a different formula is also ok

[RonaldvdH]

@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] )

                                )

                )

)