We've tried it, and it doesn't work for either of our phones, which are an iPhone Xr and a 14 Pro.
Even weirder, my wife had accidentally left her phone at her aunt's house. We weren't sure where it was at first, but tried to locate it with Find My. Despite the phone being on, Find My insisted the phone couldn't be located. Which made no sense at all since when we went back to check her aunt's house, the cell connection strength was at maximum. So, how could Find My not locate it?
I do get what you're saying, though. Just like your TV is never truly off (else, how would your remote turn it on), an iPhone is never truly off. If it were, then it wouldn't know the user tapped a button to turn it back "on".