It sounds like you're looking to introduce Sound Wave Propagation, and i expect a later question will be to introduce the Doppler effect, into your game on Unity.

I expect you will want to do this yourself, however, an asset exists to do this: https://forum.unity.com/threads/propagate-real-time-sound-propagation.339732/ the intro video that ljdp has on the linked forum post shows some insight into the challenges that you're going to face, specifically that unity doesn't (or didn't) spacialize sound well.

As you've already said, you know how to create a 'fade-out', however the Unity Audio Source API does have a bit of a discussion around how distance impacts magnitude of sound, etc. What i don't know, having not experimented with it myself, is if the sound actually propagates or if its simply is instantaneous but quieter.

For example, i dont know if:

a) The sound is delayed in any way to account for the ~344m/s propagation speed (assuming sea level), meaning a listener in the game world 344m away would hear the sound 1 second after the person making it, provided it was sufficiently loud..

or if:

b) The sound is played immediately with a de-amplification/decay curve applied to the sound to account for attenuation of that sound wave over the 344m (to use the same example above).

Hopefully the terms above help you in your searching, and the prior example gives you something to work from.

I had a quick look in the docs and there is:

AudioSource.PlayDelayed

Another way of adding the delay manually (reasonably efficiently) would be to give all your sound effects a delay (empty sound) before starting of say 1 second, then write a wrapper around the play sound function to adjust the start time of the clip according to the distance from the listener.