I created a form which has a calculated value as below:
The issue is that when we create new item, the name sometime appear as #Value! while sometimes it is working fine.
Please advise why this is happening.
That's because you've just typed in the text "Current User" instead of using a Reference.
Instead of just typing it in, use the Formula Builder to access all of the references to the built in values exposed on the Form.
First, while in the settings of the Calculated Value Control, click on the f(x) button next to the Formula Input:
Once inside there, you can type out your Function, and arguments that aren't references (userProfileLookup( ,"PreferredName"), and then fill out the 'Current User' using the Current User reference found in the Common Tab as shown. If done correctly the phrase 'Current User' will be in red as shown.
This should correct your problem.