6 Replies Latest reply on Jul 10, 2019 6:39 PM by Jeffrey Ten Grotenhuis

    How do I calculate the MIN of a set of dates that at the same time meets my filter criteria?

    Jeffrey Ten Grotenhuis

      Goal: to get the date of the first day of the current fiscal period.

       

      By combining the fields below, I can filter my data to the current fiscal period:

      • [FQ (Closed Date)] has the value "Current," which allows me to filter to the current fiscal quarter (of the current year).
      • [Current FP Number] returns the number (1-12) of the current fiscal period (as of writing this on July 9th, I am in period 8, as seen below)

       

      Capture.PNG

      I've tried adding the filters above to the Context and then making this formula

       

      MIN(

          MIN(

          DATETRUNC('week',[Close Date],'saturday')

          ,DATETRUNC('week',[Close Date]-7,'saturday')

          )

      ,DATETRUNC('week',[Close Date]-14,'saturday')

      )

       

      I designed it this way because nothing was closed last week, so if I don't add the row with -7, then the formula returns 9/6/2019. And I added the row with -14 just in case nothing is closed for two weeks.

      The problem is that the formula currently returns 6/22/2019, which is in period 7.

       

      Is there a way to return the minimum value that matches my filter criteria? In this case, the correct answer would be 6/29/2019.

       

      I'm sorry I can't share the workbook, it's confidential.

       

      Thank you for your time.