Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Error on calculating average duration days between two dates

Hi guys,   I'm facing an issue on identifiying the average duration of the difference between two columns.  On my query i've structured the formula for a new column like this:   if [DT. Deliver...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    Since there are "No" values in your Duration column, we could not directly use maths function like average() or averagex():

    So I would firstly convert it to Number type by replace the "No" with number 0:

    Measure = IFERROR(CONVERT(MAX('Table_1'[Duration]),INTEGER),0)

    Then calculate the average:

    average = AVERAGEX(FILTER('Table_1',[Duration]<>"No"),[Measure])

    Output:

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Anonymous ,

 

Since there are "No" values in your Duration column, we could not directly use maths function like average() or averagex():

So I would firstly convert it to Number type by replace the "No" with number 0:

Measure = IFERROR(CONVERT(MAX('Table_1'[Duration]),INTEGER),0)

Then calculate the average:

average = AVERAGEX(FILTER('Table_1',[Duration]<>"No"),[Measure])

Output:

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.