Can't filter a table before calculating Average

[gdelvecchio]

Average Account Life 2:=

　

AVERAGEX (

  FILTER (

    'Facts', [Close Date] > [Open Date]),

      DATEDIFF([Open Date], [Close Date], MONTH))

The measure above gives me an error saying the DATEDIFF function requires the start date cannot be greater than the end date.  So my Filter isn't working the way I think it should.  Please, someone, tell me what I'm missing.

Thanks,

Gabe

[Anonymous]

Try to work with following formula 

Measure = AVERAGEX(FILTER(Facts, 

Facts[Open Date] < Facts[Close Date]), 

DATEDIFF(Facts[Open Date], 

Facts[Close Date], MONTH))

Hop it will give correct answer.

[v-frfei-msft]

Hi @gdelvecchio,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

Regards,
Frank

[v-frfei-msft · Accepted solution]

Hi @gdelvecchio,

One sample for your refernce. Using the fomula as below, everything worked well.

Measure = AVERAGEX(FILTER(Facts,Facts[Open Date]<Facts[Close Date]),DATEDIFF(Facts[Open Date],Facts[Close Date],MONTH))

For more details, please check the pbix as attached.

Regards,

Frank