Forum Discussion

RonaldvdH's avatar
RonaldvdH
Post Patron
6 years ago
Solved

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] )
)
)
)
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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.

10 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
      saviola07
      Frequent Visitor
      6 years ago

      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's avatar
        GadeshevArman
        New Member
        3 years ago

        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.

         

         

  • AlB's avatar
    AlB
    Community Champion
    6 years ago

    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's avatar
      RonaldvdH
      Post Patron
      6 years ago

      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] )
                                      )
                      )
      )
  • MarlenePirker's avatar
    MarlenePirker
    Helper I
    3 years ago

    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