2 Replies Latest reply on Dec 21, 2015 7:37 AM by Rody Zakovich

    Monthly avg sales issue

    Jessy Cool

      How to get Monthly Avg sales  for each category .


      i use avg (sales ) but dont know how to get avg sales per month

        • 1. Re: Monthly avg sales issue
          Thomas McCullough

          Hi Jesse,

          The attached workbook shows a solution to get average monthly sales by Product Category using the WINDOW_AVG table calculation function.




          • 2. Re: Monthly avg sales issue
            Rody Zakovich

            You could also use an LoD.


            First step is to create a field that defines the Month and Year. Here are some options.


            1. Create Custom Date Field



            2. Create a calculated field that basically does the same thing


            (DATEPART('year', [Order Date])*100 + DATEPART('month', [Order Date]))


            3. Use Truncation


            DATETRUNC('month', [Order Date])


            Either of these will work, and they all give you a field to use in an LoD, i.e. Year and Month (2015-12)


            Once you have that, you can just reference it using an INCLUDE LoD


            AVG({ INCLUDE [Order Date (Month / Year)] : SUM([Sales]) })


            Since you are using an LoD, you don't need to worry about adding a LAST() function, or adding the Order Date to the Detail shelf.  And you can filter, without issues.