3 Replies Latest reply on May 1, 2018 5:54 AM by Marc-Anthony Di Biase

    Fitching the last number each month

    Lior Barak

      Hi everyone,

      I am using now a stupid formula to get the latest information of the day/week/month and wanted to know if someone has a better idea to do it

       

      IF [Time Frame] = "week" THEN

      SUM(IF DATENAME('weekday', [Date], 'Monday') = "Sunday" THEN ZN([costs]) ELSE IF [Last Day] THEN ZN([costs]) END END)

      ELSEIF [Time Frame] = "month" THEN

      SUM(IF DATEPART('day', [Date], 'Monday') = 28 THEN ZN([costs]) ELSE IF [Last Day] THEN ZN([costs]) END END)

      ELSE SUM(ZN([costs]))

       

      I am sure it's wrong and there is a better way to do it I just can't think of one at the moment that will prove working better.

      Final product: I wish to present a bar chart per month with the costs we had on the last day of this month.

       

      Any help will be appreciated

       

      Thanks

      Leo

        • 1. Re: Fitching the last number each month
          Jim Dehner

          Good morning

           

          the last day for the preceding month can be found with               DATETRUNC("month",[date])-1

           

          the datetrunc formula returns the first day of the month of Date then going back 1 will give you the last day of the preceding month

           

          Jim

          If this posts 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: Fitching the last number each month
            Jim Dehner

            BTW the last day of this month is

                           DATETRUNC("month",dateadd('month',1,today()))-1

            Jim

             

            If this posts 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.

            • 3. Re: Fitching the last number each month
              Marc-Anthony Di Biase

              You may also find a fixed formula helpful depending on how your actual data set is laid out.

               

              { FIXED month(date) : IF(DAY([Date]) = MAX(DAY([Date])) THEN sum([costs]) END}

               

              This would check if the day of the date is equal to the maximum date recorded (or the end of the month) for each month then summing up the costs.