Charge density wave order near the non-superconductor to superconductor phase transition in YBa$_2$Cu$_3$O$_{6+x}$