Forum Discussion
DATEDIFF question
That sounds like a feature where Power BI is offering you some Time Intelligence over your datetime column.
It has recognised your "Actual Response Date" column is a date, and it's building a hierarchy on the fly to allow you to drill up and down on visuals that offer visuals (eg, Bar and Column).
There should be a small 'x' to allow you to remove the levels in the hierarchy. It can be a handy feature but you don't always want it.
Ok so in the error I just posted I used the suggestion from smoupre which was :
NoDays = DATEDIFF([Actual Response Date], [Target Response Date], DAY)
Where Actual Response Date and Target Response Date were a Date/Time format and NoDays should be Whole Number
So then I went and tried the suggestion from ryans
NoDays =
DATEDIFF (
DATEVALUE ( "Actual Response Date" ),
DATEVALUE ( "Target Response Date" ),
DAY
)
with the dates set as as text
and I then get this error message :
MdxScript(Model) (3, 5) Calculation error in measure 'NAVDatabase$Request Header'[NoDays2]: Cannot convert value 'Actual Response Date' of type Text to type Date
Ok so I am an absolute idiot - of course I am getting my data from a SQL database so I forgot vital piece of information in my code
so now I have:
NoDays = DATEDIFF('NAVDatabase$Request Header'[Actual Response Date],'NAVDatabase$Request Header'[Target Response Date], DAY)
Where Request Header is my table name in my database NAVDatabase
I now get an error that says:
"A single value for column 'Actual Response Date' in table 'NAVDatabase$Request Header' 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'
I guess this is a commn error that everyone (but me) knows what it means
Phil_Seamark Hi Phil, Im having the same error message as Cazzagg is there any way to solve this error? Anyone can help?
Well now I am very confused because I have just been googling the error and it seems I maybe should be using this statement in a calculated column and not in a measure - which means I don't need the 'NAVDatabase$Request Header' in the statement - which is great
But then when I use the [revised] code in a calculated column I get the "Expression error: The name 'DATEDIFF' wasn't recognized. Make sure it's spelled correctly" despite the fact that the it says "No syntax errors have been detected" when I am in the query, but when I click OK I get the "wasn't recognized error"
I give up.
