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.
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.