Datevalue incorrect conversion

[trevb]

Blast, just realised what my issue was here!

I have a date in english form that is stored in a Parameters table so I can access it from PBI Desktop.  The original parameter is A date in power query stored as a parameter.  When it is stored it is stored as "1/8/2015" which in the UK means the 1st of August 2015.  

When I convert that using DateValue it becomes the 8th of January 2015, despite my locale being set to UK.  Suggests the function is not using my locale when it does the convert despite what the manual seems to indicate.  Now this is supposed to be a quick reference value so I would prefer to avoid doing the shifting of date elements to build the right date.  On the other hand modifying the source data so it stores in American format runs the risk that at some point the issue is fixed and suddenly my date becomes the 1st of January again and all my figures go wrong.

Anyone got any thoughts about this?

[AlexChen]

Hi,

I think you can reference article https://msdn.microsoft.com/en-us/library/ee634543.aspx.
Please see content below from this article.

The DATEVALUE function uses the locale and date/time settings of the client computer to understand the text value when performing the conversion. If the current date/time settings represent dates in the format of Month/Day/Year, then the string, "1/8/2009", would be converted to a datetime value equivalent to January 8th of 2009. However, if the current date and time settings represent dates in the format of Day/Month/Year, the same string would be converted as a datetime value equivalent to August 1st of 2009.

If you want to make DateValue(“1/8/2009”) equals “Augurest 1st 2009”. You have to change the date format setting on local machine. Please follow the steps below.

1.  Go to control panel and click “Date and Time”, choose “Change date and time”.

2.  Click link “Change calendar settings”.

3. Change “Short date” value to “d/M/yyyy”, just like below.  Click “Apply” and “OK”.

4. See test result below:

Best Regards

Alex

[trevb]

Hi Alex,

That is actually the root of my problem.  Those settings are all correct but DateValue interprets the date as an American date anyway.

[AlexChen]

Hi,

Can you give out a screenshot on your control pannel settings just like what I showed above?

Also please paste a sample code using DateValue and its execution result.

Best Regards

Alex

[gyumike]

Hi AlexChen,  do we have answer or solution for the issue from latest PBI desktop version? Thanks Mike  

[trevb]

This is the measure I am using modified so it works 😉

Funding Period Start = Date(2015,8,1) //DATEVALUE(LOOKUPVALUE(Parameters[Value],Parameters[Name],"Funding Period Start")) -conversion error means this has to go on hold

In the end I had to comment out the function and Force a block date.  The value in Parameters is "1/8/2015"

The value I was receiving from this conversion in english UK was  8/1/2016 i.e. 8th January 2016.  I've included an image of my time settings for you.