Forum Discussion

michael_knight's avatar
michael_knight
Post 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 )
    )

michael_knight's avatar
michael_knight
Post 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
Super 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 )
)