DATEDIFF question

[Cazzagg]

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?

[Phil_Seamark]

What is your datasource?  Are you getting the data from an SQL DB?

[Cazzagg]

Yes the data is from a SQL database

[Greg_Deckler]

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.

[Cazzagg]

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. 

[Phil_Seamark]

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.....

[Cazzagg]

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 

[Cazzagg]

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 ???

[Phil_Seamark · Accepted solution]

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.

[Cazzagg]

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

[Cazzagg]

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

[XiB]

@Phil_Seamark Hi Phil, Im having the same error message as @Cazzagg is there any way to solve this error? Anyone can help? 

[Cazzagg]

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.

[Cazzagg]

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'

[ryans]

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.