Forum Discussion
DATEDIFF syntax error in measure.
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.
Hi,
Does this work?
=DATEDIFF(MAX(Data[VacDate]),TODAY(),Month)
4 Replies
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
Hi,
Does this work?
=DATEDIFF(MAX(Data[VacDate]),TODAY(),Month)
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?
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.
