2 Replies Latest reply on Jan 11, 2019 6:43 AM by Damien Azzopardi

    Count weeks between two different years

    Damien Azzopardi

      Hi,

       

      I've created the following calculated field in order to count the number of weeks shown in a report: MAX(DATEPART('week', [Date])) - MIN(DATEPART('week', [Date])) + 1

       

      This field is used as a dynamic title and updates automatically when a user modifies the date filter.

       

      However, since last week and the change from the year 2018 to 2019, this calculated field now shows 53 weeks, when it should show only 4 weeks (see screenshot below).

       

      Screenshot 2019-01-10 at 3.02.22 PM.png

       

      Any idea on how to fix this issue?

       

      Thanks in advance!

       

      Damien

        • 1. Re: Count weeks between two different years
          Deepak Rai

          Do you have a workbook as description is bit confusing or you can explain a  bit more.

          • 2. Re: Count weeks between two different years
            Damien Azzopardi

            Hi Deepak,

             

            Unfortunately, due to confidentiality reasons, I cannot add any workbook. But let me add some more details.

             

            The following calc, used in the title, counts the number of weeks shown in the report: MAX(DATEPART('week', [Date])) - MIN(DATEPART('week', [Date])) + 1

             

            Up to the last week of 2018, when selecting 4 weeks, it counted 4, when selecting 8 weeks, it counted 8 etc. (see screenshots below):

            Screenshot 2019-01-11 at 2.51.05 PM.pngScreenshot 2019-01-11 at 2.53.03 PM.png

             

            Here comes the issue; when looking at a combination of weeks between 2018 and 2019, let's say the past 4 weeks, this same calc now counts 53 (weeks) when it should count 4 (weeks) (see screenshot in the original post). To be even more precise, the calc does 53-1+1 (max week - min week + 1) so this is why it returns 53 .