1 Reply Latest reply on Jul 26, 2018 9:01 AM by Joe Oppelt

    Convert Week (string) to WeekNumber (Date)

    Leon Mclean

      Hello all,


      Given my data set i am unable to get Date, but only [Week], [Month], [Year].


      These are all whole numbers E.g. [Week] = 21 // [Month] = 5 // [Year] = 2018


      As weeknumber is not recognized when i try to convert to a Date, is there a calculated field to incorporate all [Year] [Month] [Week] into one field and to be recognized as a Date dimension?


      The reason i am asking this, i am looking to create a calculated field that ultimately pulls the Latest Week and assigns revenue to that date (e.g. { FIXED [Last Week]: SUM([Revenue])}


      Many thanks,


        • 1. Re: Convert Week (string) to WeekNumber (Date)
          Joe Oppelt

          I hate weekly reporting. 


          Yup, we can't build a straightforward date based on week values.


          But consider this.  Find your starting date for the first week of your corporate calendar.  For instance, this calc:





          I just plugged in a hard-coded date of January first.  Datetrunc gives me the first day of the week that the supplied date falls in.  The result is 12/31/2017.  Assuming that's what you consider the beginning of the first week of your year, then you can do this:


          DATEADD('week',[your week value], [calculation 1])