topic Re: How to calculate which period a customer was a tenant in DAX Commands and Tips

How to calculate which period a customer was a tenant

Anonymous — Wed, 28 Sep 2022 14:21:35 GMT

Hey everyone,

Could you help with a calculation to count the number of customers who were tenants within a specific year?

For example in 2010, how many people were tenants - meaning every customer who had an active tenancy before and up to 2010 and tenant end date after 2010 and still active (blank)

I'm unable to attach the excel file which details the data. I have too many rows to PrintScreen it

Filter on Tenant Start Date to 2010

Filter on Tenant End Date after 2010

I would like to calculate this year on year to date.

Thank you for taking the time

Re: How to calculate which period a customer was a tenant

v-yinliw-msft — Thu, 29 Sep 2022 05:39:34 GMT

Hi Anonymous ,

If I understand your requests correctly, you can try this method below:

I create a sample table to do this:

And then, find the max year and the min year. Measure below:

Measure:

Min Year = MIN('Test Table'[Tenant Start Date].[Year]) Max Year = MAX('Test Table'[Tenant End Date].[Year] )

Then we can get a table between the min and the max year:

Measure:

Year Table = CALENDAR(DATE([Min Year],1,1), DATE([Max Year],1,1))

And retain the years, create a new table:

Years = ALL('Year Table'[Date].[Year])

In the “Years” table, we need to find out the number of tenant :

New a column:

Number of Customers = CALCULATE ( COUNT ( 'Test Table'[Tenant ID] ), FILTER ( 'Test Table', 'Test Table'[Tenant Start Date].[Year] <= [Year] && 'Test Table'[Tenant End Date].[Year] >= [Year] ) )

Then we can get the table below:

In the report, set a slicer with Year and set a card with Number of Customers:

Can this solve your questions?

Hope this helps you.

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: How to calculate which period a customer was a tenant

Anonymous — Mon, 03 Oct 2022 08:14:49 GMT

Thank you, v-yinliw-msft. the calculation did not work for this particular question. The numbers are less than they need to be. Is there a way to send the dataset?