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

 

I have the DAX here to find the SUM with ALLEXCEPT months but I'm struggling to find one that can grab this value but for only over 3 years ago from today?

 

Aggregation = CALCULATE(SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ),ALLEXCEPT('Date','Date'[Month]))
 
I am using Alberto Ferrari's "Rolling Average" file as a sample template
 
Thanks in advance
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.

3 Replies

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