My data is on Tennancies, see below:

Tennant Code, Start Date, End Date

I wish to have a Report like below:

Month Num of Tennancies

Jan 05 120
Feb 05 130

etc.

How can I count the number of tennancies in a particular month? Do I
need a custom rollup formula for this? How do I design the dimensions?

Any help much appreciate. It seems like such a simple problem, but I'm
finding it very difficult.

This might be easier with a SQL ETL and a month table to create a table
to report off of.
Keep in mind this is a rough draft.
The month table would be preloaded with the months you want to report
off of.

Create table rentalMonths as
(
monthID int identity, --check this syntax
rentalMonth int,
rentalYear int
)
insert into rentalMonths (rentalMonth, rentalYear)
select 1,2005
insert into rentalMonths (rentalMonth, rentalYear)
select 2,2005

Create a aggregate table
create table rentalTennancies as
(month int, year int, tennancies int)

Use your month's table and a correlated subquery to get your results.
If you share your schema I can help with query

Hope this helps.