cancel
Showing results for
Did you mean:
New Member

## Count New Tenants per month

Hi All,

I'm trying to count new tenants per month but can't really figure it out...

I have a table with these columns:

TenantID | Name | ValidFrom

I would like to have a Card for each month (or table, don't know whats most useful to display that kind of information), which displays the newly added tenants in that month.

TIA!

1 ACCEPTED SOLUTION

Accepted Solutions
New Member

## Re: Count New Tenants per month

thanks guys,

ultimately the solution was to create a card and measure for each month.

And the measure looks like this:

New January = CALCULATE(COUNT(Tenant[Name]); Tenant[ValidFrom] >= DATE(2017; 01; 01) && Tenant[ValidFrom] < DATE(2017; 02; 01) )

7 REPLIES 7
Community Support

## Re: Count New Tenants per month

You may refer to the following DAX that creates a new table.

```Table =
DISTINCT (
SELECTCOLUMNS (
CALENDARAUTO (),
"Date", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 )
)
),
"Count", 0
+ COUNTROWS (
FILTER (
Table1,
YEAR ( Table1[ValidFrom] ) = YEAR ( [Date] )
&& MONTH ( Table1[ValidFrom] ) = MONTH ( [Date] )
)
)
)
```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User IV

## Re: Count New Tenants per month

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
New Member

## Re: Count New Tenants per month

here's a sample of my source and what I would like to achieve:

New Member

## Re: Count New Tenants per month

"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

Super User IV

## Re: Count New Tenants per month

Hi,

1. Create a calendar table with dates from 1 Jan 2017 to 31 December 2017
2. Take both tables to the Data Model
3. Create a relationship from the ValidFrom column of your data table to the date column of your calendar table
4. In the calendar table, extract the year from the date by using =YEAR('calendar'[Date])
5. In the calendar table, extract the month from the date by using =FORMAT('calendar'[Date],"MMMM")
6. Create a Table visual and apply a filter on year 2017
7. Drag Month from the calendar table to the Table visual
8. Write the following calculated field formula in the Data table =COUNTA('Data'[Name])

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

## Re: Count New Tenants per month

I meant a calculated table.

https://powerbi.microsoft.com/en-us/guided-learning/powerbi-learning-2-6-create-calculated-tables/

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
New Member

## Re: Count New Tenants per month

thanks guys,

ultimately the solution was to create a card and measure for each month.

And the measure looks like this:

New January = CALCULATE(COUNT(Tenant[Name]); Tenant[ValidFrom] >= DATE(2017; 01; 01) && Tenant[ValidFrom] < DATE(2017; 02; 01) )

Announcements

#### ‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors