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.
You could create a view over your data in the SQL Database and use that as a source for Power BI
in the view you could use the SQL DateDiff function. Something like:
SELECT
DATEDIFF(DAY,[Actual Response Date],[Target Response Date]) AS NoOfDays,
*
FROM RestOfQueryHere.....
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'