2 Replies Latest reply on Jun 1, 2016 5:38 AM by JayC

    End of month Calc field - Help please

    JayC

      All,

      I have a situation where i need to show latest month&year in dashboard adding a tag "Data as of EoM April 2016" in a calculated field.

       

      I Don't have month field so cannot use max function which would have been straight forward.

      Using DATEADD('month',-1,TODAY())  will do the desired in showing date with 1 month lag.

       

      But i  have data till Apr'16 only so abv calc field will work as long as current month is May'16 but when we move to Jun'16 it will read - "Data as of EoM May 2016" which is incorrect.

      we update the tables in 2nd week of every month.

       

      Any chance of getting the required please!!

        • 1. Re: End of month Calc field - Help please
          Simon Runc

          hi Jayachandra,

           

          So we may need a couple of attempts to fine turn this to your exact update rules...but the following formula should (generally) do the job

           

          IF DATEPART('week',[Today's Date]) - DATEPART('week',DATETRUNC('month',[Today's Date]))+1<=2 THEN

          DATE(DATEADD('month',-2,[Today's Date]))

          ELSE DATE(DATEADD('month',-1,[Today's Date])) END

           

          This part of the formula...picks up the Week in Month Value (I did not know this one before trying to answer this...very hand formula!)

          DATEPART('week',[Today's Date]) - DATEPART('week',DATETRUNC('month',[Today's Date]))+1

           

          Hope that helps, but please post back if not (we can fine-tune it to your exact update schedule...assuming it's something regular like, 2nd Friday of every month).

          • 2. Re: End of month Calc field - Help please
            JayC

            Thanq very much - Worked perfectly.