Error when using datediff function in measure

[rickylee]

Hello,

I have two columns and are filled with dates. Some of them a empty. I tried to use datediff function to find the difference between two dates. 
E.g.
The time it takes to start filling in an application = DATEDIFF('gdit_assessments'[chas_datestarted],'gdit_assessments'[chas_dateentered],DAY)

But, I got an error message says "A single value for column 'chas_datestarted' in table 'gdit_assessments' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

Does anyone know why?

Thanks,
Ricky

[Anonymous · Accepted solution]

@rickylee,

Since you are using a measure, you need to aggregate your columns (MAX, MIN). In your case, it is better to use a calculated column instead.

[v-huizhn-msft · Accepted solution]

Hi @rickylee,

As @Anonymous said, please create a calculated column rather than a measure in your scenario, please get more details form this article: calculated-columns-and-measures-in-dax.

Thanks,
Angelia

[Anonymous · Accepted solution]

@rickylee,

Since you are using a measure, you need to aggregate your columns (MAX, MIN). In your case, it is better to use a calculated column instead.