new is used to create a new instance, based on an existing definition (which you are storing in Add). Instead of an instance being generated, you're returning a function, so it is not working out as you expected.
This form should work:
function Add(x) {
    this.counter = x;
    this.addOne = function() {
        return this.counter += 1;
    }
}
var add = new Add(0);
function myFunction() {
    document.getElementById("demo").innerHTML = add.addOne();
}
As to your question about why your code was returning NaN, let's look at what you were doing:
function Add(x) {
    this.counter = x;
    return function () {return this.counter += 1;}
};
var add = new Add(0);
function myFunction(){
    document.getElementById("demo").innerHTML = add(); 
}
Here you declare a variable add and initialize it using new Add(0). First, the Add function declares a property in its own scope named counter and assigns it the value passed in via the parameter named x (0, in this case). Then, it returns a function. Then it exits, no longer required, and is garbage-collected. The value assigned to the counter property is discarded because the this it is attached to has gone out of scope.
You now have add as a pointer to a function. It does not have a property named counter. When you invoke the add() function, it tries to add 1 to this.counter. Since this.counter is not defined, it automatically has the value undefined.
If you try to add a number to undefined, you get NaN. Since this happens in a return statement, NaN is returned.