Forum Discussion

OfirK1's avatar
OfirK1
New Member
4 years ago
Solved

average over period

Hi, 

  I was unable to find a straight answer to my question. The goal is to determine the average monthly sales for each product over a specified period of time. It is up to the user to determine the time frame. I would like the average to consider months regardless of whether the product was sold in the months included in the range. I created a Date table with one column of running dates in the format dd/mm/yyyy.  

for example, the table is : 

ProductDateSale price ($)
A1/1/202230
A20/1/202210
A1/3/202220
B1/2/202250
B1/5/202220
B1/7/202210

And the selected range is 2022 , 8 months (considering now is August), therefore, the expected output should be:

 

ProductAverage
A7.5
B10

 

what I actually get right now:

ProductAverage
A(30+10+20)/3= 20
B(50+20+10)/3=26.6 

 

thanks!!

 

Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, OfirK1 

     

    You can try the following methods.

    Table:

    Date = CALENDAR(DATE(2022,1,1),TODAY())

    Column:

    Year = YEAR([Date])
    Month = Month([Date])

    Measure:

    Average = 
Var N1=CALCULATE(SUM('Table'[Sale price ($)]),ALLEXCEPT('Date','Date'[Month],'Date'[Year],'Table'[Product]))
Var N2=CALCULATE(DISTINCTCOUNT('Date'[Month]),ALLEXCEPT('Date','Date'[Month],'Date'[Year]))
return
DIVIDE(N1,N2)

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • jgeddes's avatar
    jgeddes
    Super User
    4 years ago

    There are likely other ways to do this but the following measure will work.

    ProductAverage =
    var _salesSum =
    SUM(sales_table[Sale price ($)])
    var _selectedDate =
    SELECTEDVALUE(dimDate[Date].[MonthNo])
    var _numberOfMonths =
    ((YEAR(TODAY()) - SELECTEDVALUE(dimDate[Date].[Year])) * 12) + _selectedDate
    Return
    DIVIDE(_salesSum,_numberOfMonths,0)

     

  • OfirK1's avatar
    OfirK1
    New Member
    4 years ago

    thanks jgeddes , Im not sure it does what I want.  I'm getting a result only if I pick specific month (and year), the rest are 0's.  I want to get the monthly average of the chosen period. 

    • jgeddes's avatar
      jgeddes
      Super User
      4 years ago

      The measure as it is currently written will only return a value if there is a date to evaluate against. If you want to return a default value it could be added to the measure with if statements.

  • v-zhangti's avatar
    v-zhangti
    Community Support
    4 years ago

    Hi, OfirK1 

     

    You can try the following methods.

    Table:

    Date = CALENDAR(DATE(2022,1,1),TODAY())

    Column:

    Year = YEAR([Date])
    Month = Month([Date])

    Measure:

    Average = 
Var N1=CALCULATE(SUM('Table'[Sale price ($)]),ALLEXCEPT('Date','Date'[Month],'Date'[Year],'Table'[Product]))
Var N2=CALCULATE(DISTINCTCOUNT('Date'[Month]),ALLEXCEPT('Date','Date'[Month],'Date'[Year]))
return
DIVIDE(N1,N2)

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • OfirK1's avatar
      OfirK1
      New Member
      3 years ago

      Hi, I get this error:

      All arguments within an ALLEXCEPT function must be related to (or contained by) the table which is used as the first argument. Where a one-to-many relationship exists, the table which is used as the first argument must be on the 'many' side of that relationship.

      in my case, its one-to-many relationship between Date and Table (many products can be sold in one date).