This isn't the default behavior for whatever reason, and it's something I've just silently dealt with for years. Because of this default functionality, instead of using Cmd-N I've always used the GUI buttons for creating new files while working on some project. I didn't think an alternative existed, but I've finally found that one does.
As always, Cmd is more or less equivalent to Ctrl on Windows, so odds are it'll be a direct swap in the combos I've listed.
It's not a difficult fix; I just didn't bother to even look for one. We'll be editing keyboard shortcuts to accomplish this, so hit Cmd-Shift-P to open up the command panel and then type "key" to pare down the list. Select the one that says "Preferences: Open Keyboard Shortcuts", and in the search box put "new file" to find relevant shortcuts.
The second shortcut in the list, "File: New Untitled Text File" is the default and "wrong" one to be set to Cmd-N. What we want is that last one, "File: New File…" to be set to that instead. You can right-click the existing one or do Cmd-Delete while it's selected to reset it. Then you can double-click the desired one for the shortcut prompt. Hit Cmd-N, save it, and you're golden.
The "…" on the latter shortcut simply means there's more than one step. In this case it will prompt you for a file name over in the file explorer ⸺ which is precisely what we're looking to do!
Whichever folder is currently active in your sidebar, called the "Explorer" by VS Code, is where the new file will land. Cmd-Shift-E will bring keyboard focus to it, and from there you can arrow around your folders to the correct one before doing your new Cmd-N. Easy and peasy.