Ah yes, now I remember. I was able to do this on my Pie 3 B, but it had been so long I couldn't find it again (even though it was staring me right in the face). Was too focused on the terminal commands that I forgot about this menu. Simple and effective. Thanks!
Starting the service as early as the 'local-fs' target doesn't always work, if the framebuffer is not initialized, then you can't display anything. For the Pi4, the display is re-initialized when the KMS/DRM framebuffer emulation is loaded, so any images shown before that will simply be erased.
You can try using the modifying the existing asplashscreen service (defined in /etc/systemd/system/asplashscreen.service) and see if changing the dependencies will make it start earlier, but the result might not be what you want.
Most likely the USB stick is not 'mounted' (therefore inaccessible) when the splashscreen starts, so the video cannot be read and the splashscreen fails. Copying it to /opt/retropi.. will copy the splashscreen on the SD card, which is immediately available on boot.
When using a USB drive through the auto-move capability of RetroPie, the entire RetroPie folder is read from the USB drive; i.e. it "replaces" the RetroPie folder on the boot sdcard.
The videos you place in the USB folder of RetroPie/splashscreen (which becomes "/home/pi/RetroPie/splashscreen") can be read by the Splashscreen functionality, and even randomized - with some caveats I'll note below.
You can always move the video splash screens to the sdcard folder of "opt/retropie/supplementary/splashscreen" and use them from there as well. You can do that in a number of ways: from the command line (F4) then copy or move the files from the "/home/pi/RetroPie/splashscreen" folder to the "opt/retropie/supplementary/splashscreen" or do the same through SSH or through the built-in File Manager application from the RetroPie settings subfolder.
Hint: sudo cp "/home/pi/RetroPie/splashscreen/whatever your video.mp4" "/opt/retropie/supplementary/splashscreen/whatever your video.mp4" wil copy the file. Replace cp with mv if you want to move the file. You must do this as SU (sudo) because the opt folder allows only root read/write access
Caveat 1 for randomizing: Best to append the video files to the splashscreen list, and then randomize off that list
Caveat 2 for randomizing: Frequently the very first file name on the splashscreen list will be multiple files all in one long line. Recommend deleting that entire line and manually typing in the first line.
Editing the splashscreen list is fairly easy.
A. In the Splashscreen settings, go to "Append splashscreen to list"
B. Choose Own/Extra splashscreens (if your files are in the home/pi/RetroPie/splashscreen folder) or RetroPie splashscreens (if you moved them to the opt/retropie/supplementary/splashscreen folder)
C. Select the line you want to append (add to) the list and hit OK.
D. Hit Ok again to confirm the appended line
E. Repeat B-E for all the files you want to add to the list.
Again - I recommend after you're done appending the files that you go back to "Manually edit splashscreen list" and make sure that first line has ONLY the one file on it. I also again recommend you manually type that one in. It might be easier to do this after you've appended a few files because you can just copy one of the lines you appended as the first line, and then delete the original (now duplicated line.)
Once your splashscreen list has all the videos you want in it, one vide per line, go back to Disable splashscreen randomizer and enable it (or disable and then re-enable it.) You'll want to select the "Randomize /etc/splashscreen.list" selection.