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.
Not sure about your syntax here, I believe your formula should be:
NoDays = DATEDIFF([Actual Response Date], [Target Response Date], DAY)
Actual Response Date and Target Response Date should be a Date/Time format and NoDays should be Whole Number.
Thank you so much all of you for responding so promptly. I told you I am very new at this. What you are saying makes sense. I will give both options a try and let you know how I go. Thanks again this is great.
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 ???