Forum Discussion
DATEDIFF question
Hi All
I am extremely new to all this so please forgive me my ignorance......
I have two date fields in my table– one for the target response date and one for actual response date - I am trying to use DATEDIFF to take one date from the other to arrive at the number of days difference (if any)
So my statement looks like this:
NoDays = DATEDIFF ("Actual Response Date","Target Response Date",DAY)
eg Target Response Date = 11/8/15
Actual Response Date = 11/8/15 - interval = 0 day therefore actual response was within target
Or
Target Response Date = 11/8/15
Actual Response Date = 12/8/15 - interval = 1 day. So therefore actual response was one day overdue
I changed the data type in the date fields to "whole number" (as suggested in a post I saw) and my statement seems to be accepted ok but if I use the measure in my canvas, I am getting data error re converting text to date. I have tried also changing data type on my date fields to"Date" but still no go.
I think it is the formatting on my fields but am giving up as to what to set the data type to as I have tried just about everything - though it could be my code, as I said I am very new to all this.
Can anyone out there help please?
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.
14 Replies
Can you screen shot the error?
Or perhaps try something like this,
NoDays = DATEDIFF ( DATEVALUE ( "Actual Response Date" ), DATEVALUE ( "Target Response Date" ), DAY )However, that would not work if the fields are set at dates only if they are set as text.
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.....
What is your datasource? Are you getting the data from an SQL DB?
Yes the data is from a SQL database
