Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Last 3 months average

I want to get the last 3 months (ending on last month's last date from the current date) average and I have been using this measure for that. But I have doubts about that. Please someone kind enough to correct this or confirm this measure is correct.

 

3M avg pri sales QTY =
CALCULATE (
    AVERAGEX (
        VALUES ( DimDate[Month] ),
        CALCULATE ( SUM ( FactPrimarySales[UnitQty] ), DimDate[Last 3 months] = 1 )
    )
)

 

Thank you for your help.

 

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , try like this example. This will give you monthly avg

     

    Rolling 3 = divide( CALCULATE(sum(Sales[Sales]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date]),-3,MONTH)) ,
    CALCULATE(distinctCOUNT('Date'[Month Year]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-3,MONTH), not(isblank((Sales[Sales])))))

     

    Line level Avg

     

    Rolling 3 = CALCULATE(Average(Sales[Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , try like this example. This will give you monthly avg

     

    Rolling 3 = divide( CALCULATE(sum(Sales[Sales]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date]),-3,MONTH)) ,
    CALCULATE(distinctCOUNT('Date'[Month Year]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-3,MONTH), not(isblank((Sales[Sales])))))

     

    Line level Avg

     

    Rolling 3 = CALCULATE(Average(Sales[Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      amitchandak 

      Thank you for your answer. Could please explain why is better to use SUM instead of AVERAGEX.