2 Replies Latest reply on May 9, 2018 12:17 PM by Joe Oppelt

    Beginning of month measure - End of month measure

    stephen.cavallaro.0

      Hello. I have a measure and I want to take the difference between the measure for the first day of the month and the measure for the last day of the month. I can't seem to get this to work out.

        • 1. Re: Beginning of month measure - End of month measure
          Wesley Magee

          Stephen,

          You're calculation might looking something like this:

           

          SUM(IF  {FIXED DATETRUNC('month', [Date]) : MAX([Date])} = [Date] THEN Sales END)

          - SUM(IF DAY([Date]) = 1 THEN [Sales] END)

           

          It will be dependent on having the first and last day of the month in your data.

          -Wesley

           

          If this post assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Beginning of month measure - End of month measure
            Joe Oppelt

            IF [date] = DATETRUNC('month',[Date]) then [Measure] END

             

            That gets you the value for [Measure] on all rows where the [date] is the first of the month.

             

            DATETRUNC truncates the date value you give it to the beginning of whatever period you specify.  So doing 'month' on a value of April 15, 2018 will result in April 1, 2018.  Therefore only the rows where [date] = April 15 will get captures.  (All other rows get NULL.)  Then SUM([that calc]) will give you the sum for the first day of every month.

             

            To get the date for the last day of the month, do this:

             

            DATEADD('month', 1,. DATETRUNC('month',[date])) -1

             

            The inner datetrunc gives you the first of the month.  The dateadd adds a month, and the -1 at the end of the whole mess subtracts a day.  So April 15 becomes April 1, then dateadd changes it to May 1, then the -1 changes it to April 30.

             

            So:

             

            IF [date] = DATEADD('month', 1,. DATETRUNC('month',[date])) -1 then [Measure] END

             

            That will grab all data on the last day of any month.