5 Replies Latest reply: Jan 3, 2018 11:57 PM by kaan erisen RSS

    Count Distinct HELP!!!

    Mario Cueva

      Hello!!

       

      I'm trying to use the following expression to express the sales avg considering the number of months.

       

      Example:

       

      Sum Sales in last 11 years = 654,229,440

      Months for the period of time selected = ex: 11 years = 132 months (I have an expresión called Year-Month for every sales month "2017-01-Jan","2017-02-Feb" etc)

       

      My expression is: =Sum(Subt)/(Count(DISTINCT{$<[Year]-={'2006'}>}[Year-Month]))

      If there's no selection the expression works

       

      654,229,440 / 132 = average sales per month

       

      But when I select one specific year or more it still divides sales by 132 months, considering that if I select 1 year the formula should be:

       

      654,229,440 / 12 = average sales per month


      Or if I select 2 years:

       

      654,229,440 / 24 = average sales per month


      And so on, what am I doing wrong?