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.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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.