4 Replies Latest reply on Jun 12, 2018 7:59 AM by Naveen B

    Monthly YTD average calculation

    Ben Adam

      Hi,

       

      Hoping someone can help me with this one ................

       

      I want to create a field that will calculate a rolling monthly YTD average figure e.g.

      Jan = 10

      Feb = 20

      Mar = 30

       

      Average for Jan would be 10; average for Feb would be 15 [(10+20)/2]; average for Mar would be 13.33 [(10+20+30)/3] and so on.

       

      I have been trying a couple of options but think I'm missing a step ... can anyone advise?

       

      Thanks,

      Ben

        • 1. Re: Monthly YTD average calculation
          Dylan Bergey

          Look into use  a running total table calculation Keep the measure value as sum([value]), but then right click on this measure, add table calculation, calculation type = running total, using average.

           

          Let me know if I need to post screen shots.

          • 2. Re: Monthly YTD average calculation
            Bryce Larsen

            Hi Ben,

            It depends on how your data is actually stored and how you're planning to display this. If it's only monthly level rows and you're only showing the average on a sheet (not including monthly level rows), then you should just be able to do:

            SUM([Measure]) / COUNTD([Date])

             

            If you have multiple dates per month, you could alter this to:

            SUM([Measure]) / COUNTD(DATETRUNC("month", [Date]))

             

            Difficult to say what else might be needed without seeing some data or a workbook, but hopefully this can at least get you started!

             

            Best,

            Bryce

            • 3. Re: Monthly YTD average calculation
              Jim Dehner

              Good morning Ben

              see the attached

              You didn't include a twbx workbook s I used superstore data that you can adapt

              this formula will calculate the moving avg from the first of the year to the month in the record

              it is set to restart every year

               

               

              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.

              • 4. Re: Monthly YTD average calculation
                Naveen B

                Hi Ben,

                 

                I have taken the Sample superstore data to analyze your issue. Kindly Follow the below steps to achieve the solution

                 

                1) I have Created a View Month Wise Sales

                2) Drag the Sum(Sales) again to the view and Select the table calculation as Moving Average

                 

                3) You Can See the YTD Average as below

                 

                 

                 

                 

                 

                 

                January - 94925 , Moving Average: 94925

                February - 59751, Moving Average : (94925+59751)/2=77338

                March - 205005, Moving Average : (94925+59751+205005) = 119894

                 

                Hope this Helps, Kindly mark this question as Correct answers if it resolves your issue.