Active numbers per quarter

[RichOB]

Hi, I need to obatin the quarterly figures for active tenants per quarter. I have a date table with the quarter already.

In this scenario:

I have properties where tenants have stayed short-term and long-term.

When the End_date is blank, I need today's date to be inserted.

If a tenant is active for a year, they would be counted as 1 for each quarter.

The financial year runs Apr-Mar.

Using the table below, it should read as Q1 = 8. Q2 = 10. Q3 = 12. Q4 = 10.

Tenant_ID	Property	Start_Date	End_Date	Status
1	1 marble way	01/04/2023		Current
2	2 spring drive	01/04/2022		Current
3	5 manor close	01/04/2021		Current
4	7 queen road	01/04/2020		Current
7	1 ocean ave	01/04/2024	01/05/2024	Historic
8	1 ocean ave	02/05/2024		Current
9	2 ocean ave	10/04/2024	10/10/024	Historic
0	2 ocean ave	11/10/2024		Current
11	3 ocean ave	01/04/2024		Current
12	1 park way	01/07/2024		Current
13	2 park way	01/07/2024	01/08/2024	Historic
14	3 park way	02/08/2024		Current
15	1 main road	01/11/2024	10/11/2024	Historic
16	1 main road	11/11/2024		Current

Thanks for your help!

[danextian · Accepted solution]

Hi @RichOB 

I'm seeing a different number for Q4

Count by Time Period = 
VAR StartDate =
    MIN ( Dates[Date] )
VAR EndDate =
    MAX ( Dates[Date] )
RETURN
    COUNTROWS (
        FILTER (
            Tenant,
            Tenant[Start_Date] <= EndDate
                && COALESCE ( Tenant[End_Date], TODAY() ) >= StartDate
            )
        )

Please see the attached pbix. 

[v-tejrama]

Hi @RichOB ,
Thanks for reaching out to the Microsoft fabric community forum.

I have tested your scenario in Power BI and was able to reproduce the expected results for active tenants per quarter (Q1 = 8, Q2 = 10, Q3 = 12, Q4 = 10) exactly as you mentioned.

Here’s how I achieved it:

First, I created a proper Date table covering your financial year (Apr to Mar) and marked it as a date table.

Then, I created an inactive one-to-many relationship between the Date table and your Tenants table (Date → Start_Date) because we needed to handle the date logic manually in the DAX formula.

For the active tenant calculation, I used this DAX measure:

Active Tenants =
VAR SelectedDate = MAX(DateTable[Date])
RETURN
CALCULATE(
DISTINCTCOUNT(Tenants[Tenant_ID]),
FILTER(
ALL(Tenants),
Tenants[Start_Date] <= SelectedDate &&
(ISBLANK(Tenants[End_Date]) || Tenants[End_Date] >= SelectedDate)
)
)

Finally, I used a Matrix visual with Quarter on Rows and this measure in Values, and filtered by the required financial year. It gave me the correct result matching your expected output.

If the response has addressed your query, please  "Accept it as a solution" and give a "Kudos"  so other members can easily find it.

Best Regards,
Tejaswi.
Community Support

[v-tejrama]

Hi @RichOB,

I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.

If the response answered your query, kindly “Accept as Solution” and Give “Kudos” to help others in the community benefit from it as well.

Thank you.

Tejaswi.

[v-tejrama]

Hi @RichOB ,

I hope the information provided has been useful. Please let me know if you need further clarification or would like to continue the discussion.

Thank you.

[v-tejrama]

Hi @RichOB ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and Accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.