Forum Discussion

michael_knight's avatar
michael_knight
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

3 Month Average, excluding current month

Hi,   I'm trying to create a measure which will give me an average of the previous 3 months, but exclude the current month. For example, for May 2021 I want that to be an average of February 2021, ...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    michael_knight , sorry, my mistake. Try like

     

    CALCULATE (
    AVERAGEX ( VALUES ( 'Date'[Month/Year] ),[Withdrawal %]),
    DATESINPERIOD ( 'Date'[Full Date], eomonth ( max('Date'[Full Date]) ,-1), -3, MONTH )
    )

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

michael_knight , Try  like

 

3 Month Rolling Average Fall Through =
IF (
ISBLANK ( [Withdrawals]),
BLANK (),
CALCULATE (
AVERAGEX ( VALUES ( 'Date'[Month/Year] ),[Withdrawal %]),
DATESINPERIOD ( 'Date'[Full Date], eomonth ( 'Date'[Full Date] ,-1), -3, MONTH )
)
)

 

 

or only

 

CALCULATE (
AVERAGEX ( VALUES ( 'Date'[Month/Year] ),[Withdrawal %]),
DATESINPERIOD ( 'Date'[Full Date], eomonth ( 'Date'[Full Date] ,-1), -3, MONTH )
)

 

make sure Date is marked as date table

  • michael_knight's avatar
    michael_knight
    Icon for Post Prodigy rankPost Prodigy
    5 years ago

    Hi amitchandak 

     

    I tried both suggestions and got this error

     

    "A single value for column 'Full Date' in table 'Date' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

     

    I made sure to mark Date as a date table too 

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      5 years ago

      michael_knight , sorry, my mistake. Try like

       

      CALCULATE (
      AVERAGEX ( VALUES ( 'Date'[Month/Year] ),[Withdrawal %]),
      DATESINPERIOD ( 'Date'[Full Date], eomonth ( max('Date'[Full Date]) ,-1), -3, MONTH )
      )