DATEDIFF syntax error in measure.

[steambucky]

Hello I am doing some wrong here....

All i am trying to do create a measure that calculates the months since animals have been vacinated.

This is the what i am writing:

Since vac = DATEDIFF(NOW(),(VacDate),Month)

I tried this as well but it doesnt like that either....any ideas?

Since vac = DATEDIFF(NOW(),(Sheet1[VacDate]),Month)

The message I get is: A single value for column 'VacDate' in table 'Sheet1' 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.

The data type of VacDate is date and and I am confused the calc is using DATEDIFF....

Marc.

[Ashish_Mathur · Accepted solution]

Hi,

Does this work?

=DATEDIFF(MAX(Data[VacDate]),TODAY(),Month)

[steambucky]

Hello Ashish,

Your suggestion DID work with some small modifications. Thank you for your time.

I am curious about the following -  This is what finally worked.

(My new Measure)  Since last Vac = DATEDIFF(MAX(Sheet1[VacDate]),TODAY(),Month)

This also worked in a new column:

(New Column) Since last Vac = DATEDIFF([VacDate],NOW(),Month)

But the same but if swap that code between the column and measure is doesnt work. Any idea why?  

[Ashish_Mathur]

You are welcome.  In a calculated column formula, [VacDate] picks up the value in the current row of that column column.  In a measure, when you mention [VacDate], it refer to all entries in that column.  To get it to refer to the current row, we use the MAX function.

[Greg_Deckler]

If VacDate is a column, then you need to wrap it in an aggregator like MAX or MIN. If it is a measure, then [VacDate]. 

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490