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. Delivery]<[#"DT Availability"] then "No" else Duration.Days([DT. Delivery]-[#"DT Availability"]))

 

and on my dashboard, in order to find the average duration value without the No row this is the formula

 

Measure 5 = 
CALCULATE ( 
    AVERAGEX ( Table1, CONVERT ( Table1[Duration], INTEGER) ),
    Table1[Duration] <> "No"
)

 

This is the db I'm using

Link 

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.

1 Reply

  • 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.