Forum Discussion

StuartSmith's avatar
StuartSmith
Icon for Power Participant rankPower Participant
6 years ago

Reverse WeekNum to convert Calendar week to date

Is there a way, similar to WeekNum, but in reverse and convert Calendar Weeks to Dates?

The Calendar week format coming from the data source is 01.2020.

Thanks in advance.

Need Help

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Sure, the basic technique is:

     

    Date Column = 
  VAR __Year = [Year]
  VAR __Week = [WeekNum]
  VAR __Calendar = 
    ADDCOLUMNS(
      CALENDAR(DATE(__Year  - 1,1,1),DATE(__Year + 1,12,31)),
      "__WeekNum",WEEKNUM([Date])
    )
RETURN
  MINX(FILTER(__Calendar,[__WeekNum] = __Week),[Date])
    • StuartSmith's avatar
      StuartSmith
      Icon for Power Participant rankPower Participant
      6 years ago

      Thanks, and sorry for the delay in getting back to you. First question, I notice the date column is showing 2019, when it should be 2020.  

       

      I had a play with your code and the reason the year is 2019 is because of "-" in the below line, but dont know how to correct.  

       

      CALENDAR(DATE(__Year - 1, 1,1),DATE(__Year + 1,12,31)),
       
      Also, I would like the calendar week to start in the Monday, but the code is making the week start on the Tuesday.  So as an example C\W 13 should be 23/03/2020 and C\W 15 should be 06/04/2020.
       
      Thanks in advance,
       
      • StuartSmith's avatar
        StuartSmith
        Icon for Power Participant rankPower Participant
        6 years ago

        Does anyoen know why the year is showing 2019, and not the actual year in the column, 2020?  Plus, how to change th calendar week begin date from the Tuesday to the Monday?