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.

v-zhangti's avatar
v-zhangti
Community Support
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
    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).