Forum Discussion

RichOB's avatar
RichOB
Icon for Post Partisan rankPost Partisan
1 year ago
Solved

Active 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 ...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    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. 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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

 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.