You’re balancing a few things. As TomL pointed out, center of buoyancy. Then, you can think about a weather vane in terms of one side catching more wind than the other, and the boat spinning to leave that side downwind. Then, you can add to that that the balance in the wind changes as your paddling speed changes. For example, you could sit in the stern of a motionless tandem where the bow would readily blow downwind, and with a solid forward pace, it would become neutral with that wind abeam.
Next you add waves which at any quartering angle will act unevenly along the length of your hull.
All factors together, given the need to sit far off the center of buoyancy, sitting in front with the stern blowing down wind sitting still, or weather cocking, can be less manageable than sitting still in back with the bow blowing downwind, or lee cocking, because forward speed can neutralize the lee cocking. Forward speed worsens the weather cocking. Add to this that in calm, a stern will want to move around an overweighted bow in motion, and a bow will want to stay in front of an overweighted stern in motion.
And the ideal forward to aft positioning will likely vary somewhat depending upon the weight and weight distribution, the hull shape and volume distribution, the wind, waves, and current, and paddling speed. If the hull shape and volume are given with a particular boat, this variable becomes fixed. But since conventional wisdom was mentioned, I figured the boat remains variable.