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.
That's a good option too. Thank you
i will let you know how I go. Thanks again for taking the time to answer. Really appreciate it
Well well well - I am using a Microsoft Dynamics NAV SQL database as my data source by the way - and I have found something weird with the dates that are coming through in my data model
ie the field for "Actual Response Date" is called just that in the data model and in Query
but when you put the field into the canvas say in a table visualisation then the date is shown as 4 separate columns for Year, Quarter, Month, Day. (I tried to insert a screen shot to show you what I mean but couldnt get it inserted)
I have tried changing the data type in the query but doesn't matter what I change it to – even text - it ends up like below. I checked a lot of other date fields in various tables from Dynamics NAV and they all seem to do the same thing????
Am I being extraordinarily dense or is this normal ???
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.
Thanks for that
So I went in and used the little X to get rid of the Quarter column but then I was still left with three separate columns for Year, Month and Day and of course they dont have any indication about whether they are for Actual Response Date or for Target Response Date
However, I found that by right mouse clicking on the field I could then choose other options in the way it shows
But I am now getting the error below
"The value for 'Actual Response Date' cannot be determined. Either 'Actual Response Date' doesn't exist or there is no current row for a column named 'Actual Response Date'
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