Average between dates

[Syndicate_Admin]

Hello!

I am calculating the average between 2 dates (application start date and closing date). Create a column to calculate the duration with the following formula: Duration = DATEDIFF('Table'[Start Date],'Table'[Dated],DAY)

The problem is that there are cases that are still open and do not have a closing date, therefore they are blank. But when calculating the averages with the function Average Attention Span (Days) = AVERAGE('Table'[Duration])

It is resulting in a value much higher than the real average.

EI calculated the value manually and it gives me 14.83.

How can I solve this?

Thanks for the help.

[Ashish_Mathur · Accepted solution]

Hi,

Assuming Duration is a calculated column formula, edit the formula to:

Duration = if(isblank(Table[dated]),blank(),DATEDIFF('Table'[Start Date],'Table'[Dated],DAY))

The average should not be correct.

[lbendlin]

You can solve this by defining what should happen in such a case - should the data be excluded, or should the missing Close Date be substituted with "today"?