In Biblical times, censuses were taken for two basic reasons: taxation, and war. David took the census for one (or both of) those reasons, which was against the will of God.
Israel, at the time, was a fragile nation, having barely captured a capital (Jerusalem), thereby uniting north and south, and then driven out the Philistines, which threatened its western border. The country needed a period of peace and prosperity after these tremendous exertions.
Basically, David had won his earlier battles with an all-volunteer army. He told the people of Israel what he wanted to do (capture Jerusalem, drive out the Philistines), and through the grace of God, just enough of them volunteered for these projects. Later on, David wanted to conquer neighboring lands and peoples, which created two problems: 1) He would introduce foreign, non-believing people into Israel and 2) he would shed Israeli blood for this purpose. Worst of all, the purpose of the census was that David could DRAFT the soldiers he thought he'd need, instead of trusting God to give him "just enough" (along with God's power to "veto" certain projects by giving David "not enough").
But buoyed (and probably made overconfident) by his successes, David tried to push ahead and expand his kingdom for HIS wealth and glory (not God's). That was the sin.