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 ?   ...
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.
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.

       

       

      • Schwurblini's avatar
        Schwurblini
        Frequent Visitor
        3 years ago

        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?