AVERAGEX and "A table of multiple values was supplied where a single value was expected."

[stuieb]

Hi all

I'm trying to do what I thought was a straightforward moving average calculation based the rate of crashed deals over the previous 3 months.

I've created a measure with the following DAX

CrashTrend = AVERAGEX(FILTER(Opportunity, DATESINPERIOD(Opportunity[Date_Off_Market__c], Opportunity[Date_Off_Market__c], -3, MONTH)), [CrashRate])

Where CrashRate is a measure that calculates the divides the number of crashed deals over the gross number of sales.

When I add this to a report I get a "A table of multiple values was supplied where a single value was expected." error.

Any ideas why it's not just returning a scalar value?  I'm confused by this, I've tried making this a calculated column and it returns a circular reference error.

Thanks

PS.  I'm obviously still very green with DAX, there's probably some conceptual problems here. 🤔

[AlB]

Hi @stuieb 

The second argument for DATESINPERIOD has to be a single value (start date). You are passing exactliy the same as the first argument, which seems to be a full column. You'll probably need to use SELECTEDVALUE( ), MAX( ) or something similar depending on what you want to do and where your measure is used

Please mark the question solved when done and consider giving kudos if posts are helpful.

 Cheers 

[stuieb]

Thank you for replying @AlB 

This is probably my incorrect thinking.  But what I am trying to pass to DATESINPERIOD is the current column value over the interated rows, that is to say, the current value of the date field in the row contex

If that's not possible, how could I calculate back to get an average for the previous 3 months?

Regards

[AlB]

@stuieb 

Have you tried what I suggested?

CrashTrend = AVERAGEX(FILTER(Opportunity, DATESINPERIOD(Opportunity[Date_Off_Market__c], MAX(Opportunity[Date_Off_Market__c]), -3, MONTH)), [CrashRate])

If it doesn't work, you probably need to add an ALL to Opportunity in the first argument of FILTER. Otherwise I would need to see more details fo the data model to be able to come up with an accurate answer. Ideally a pbix that reproduces the problem

Please mark the question solved when done and consider giving kudos if posts are helpful.

 Cheers 

[stuieb]

Hi @AlB 

Yes, I have tried and unfortunately it doesn't work.  The error is the same, wrapping the Opportunity in ALL() doesn't make a difference.