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