Forum Discussion
Post PartisanActive numbers per quarter
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!
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.
5 Replies
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 SupportHi 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.
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.
- danextian
Super User
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.
