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 t...
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.

jgeddes's avatar
jgeddes
Icon for Super User rankSuper 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)