Measure = single value cannot be determined

[Anonymous]

I am trying to calculate a moving average with the following measure:

CL1_30DAY_MA = averagex(datesinperiod(Sheet1[Date],lastdate(Sheet1[Date]),-30,day),[CL1])

However, I am getting an error "A single value for column 'CL1' cannot be determined.  This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as a min, max, count, or sum to get a single result.  

Any ideas on how I could get this to work?

Thanks in advance!

[v-cherch-msft · Accepted solution]

Hi @Anonymous

You may try below measure:

CL1_30DAY_MA =
CALCULATE (
    AVERAGE ( Table[CL1] ),
    DATESINPERIOD ( Sheet1[Date], LASTDATE ( Sheet1[Date] ), -30, DAY )
)

Regards,

Cherie