Forum Discussion
Datevalue incorrect conversion
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
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.
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
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.
Hi AlexChen, do we have answer or solution for the issue from latest PBI desktop version? Thanks Mike
