Forum Discussion

gdelvecchio's avatar
gdelvecchio
Regular Visitor
7 years ago
Solved

Can't filter a table before calculating Average

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

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    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

3 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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.