Local port forwarding is good when you want to use SSH to pivot into a non-routable network. But if you want to access services on a network when you can't configure port-forwarding on a router and don't have VPN access to the network, remote port forwarding is the way to go.
Remote port forwarding excels in situations where you want access to a service on an internal network and have gained control of a machine on that network via a reverse shell of some kind. Whether you're a pentester or system admin, this is a good thing to know about.
For example, let's say you compromise a public terminal in the local library and get some credentials. You install a persistent reverse shell of some sort, which communicates back to your machine, but you don't have access to other services on the machine. The victim machine might have an SQL instance configured on localhost only that you want access to, or maybe you want to access the remote desktop. Maybe the network is hosting some sort of admin panel you'd like to poke around in. Whatever it is you want, a compromised host and SSH will get you in.