Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Aggregate Month values over Several Years

I have a table of sales that I want to get the average of each month over 3 years.   E.g.  If 'Date'[Month] = January then return the AVERAGEX(VALUES) of Sales for Jan 2007, Jan 2008, Jan 2009   ...
Need Help
  • Icey's avatar
    Icey
    4 years ago

    Hi Anonymous ,

     

    Try this:

    Aggregation =
CALCULATE (
    AVERAGEX ( VALUES ( 'Date'[Calendar Year] ), [Sales Amount] ),
    'Date'[Month] = MAX ( 'Date'[Month] ),
    DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -3, YEAR )
)

     

     

     

    Best Regards,

    Icey

     

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

amitchandak's avatar
amitchandak
Super User
4 years ago

Anonymous , Try like

Aggregation = CALCULATE(AverageX (values('Date'[Month]),calculate( sumx(Sales, Sales(Sales[Quantity] * Sales[Net Price] ))),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,Year)))

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I've tried this:

    Aggregation = 

    CALCULATE(
       AVERAGEX (VALUES('Date'[Month]),
             CALCULATE( 
             CALCULATE(SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )),
             DATESINPERIOD('Date'[Date],MAX('Date'[Date]),
       -3,YEAR)))
     
    but the following error occurs "Something's wrong with one or more fields: (Sales Aggregation: The end of the input was reached."

     

    • Icey's avatar
      Icey
      Community Support
      4 years ago

      Hi Anonymous ,

       

      Try this:

      Aggregation =
CALCULATE (
    AVERAGEX ( VALUES ( 'Date'[Calendar Year] ), [Sales Amount] ),
    'Date'[Month] = MAX ( 'Date'[Month] ),
    DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -3, YEAR )
)

       

       

       

      Best Regards,

      Icey

       

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