Calculating active figures

[RichOB]

Hi, in my scenario, I have some apartments where people have short and long term stays, I want to obtain the figures of active tenants per financial quarter (I already have a date table). Using the table below I need to show

Q1 = 4

Q2 = 5

Q3 = 6

Q4 = 6

Tenant_ID	Property	Start_Date	End_Date	Status
1	1 ocean ave	01/04/2024	01/05/2024	Historic
2	1 ocean ave	02/05/2024		Current
3	2 ocean ave	10/04/2024	10/10/024	Historic
4	2 ocean ave	11/10/2024		Current
5	3 ocean ave	01/04/2024		Current
6	1 park way	01/07/2024		Current
7	2 park way	01/07/2024	01/08/2024	Historic
8	3 park way	02/08/2024		Current
9	1 main road	01/11/2024	10/11/2024	Historic
10	1 main road	11/11/2024		Current

Thanks

[v-pagayam-msft]

Hi @RichOB ,
Thank you for the helpful responses @MFelix , @Ashish_Mathur  and @Elena_Kalina !
Have you got an opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you for being part of the Microsoft Fabric Community.

[Ashish_Mathur]

Hi,

Based on the 10 row table that you have shared, show the expected result.  The numbers that you have shown in each quarter do not seem to tie up with the 10 row table.

[Elena_Kalina]

Hi @RichOB 

Certainly! Let me clarify the quarterly calculation step by step using your table to understand why you're getting different results (Q1=4, Q2=5, Q3=6, Q4=6).

Key Rules for Active Leases per Quarter A lease is considered active in a quarter if: Start_Date ≤ Last day of the quarter AND (End_Date is empty or End_Date ≥ First day of the quarter).

Quarter Dates in 2024

Q1: Jan 1 – Mar 31

Q2: Apr 1 – Jun 30

Q3: Jul 1 – Sep 30

Q4: Oct 1 – Dec 31

My results: Q1= 0, Q2= 5, Q3= 3, Q4= 3

[RichOB]

Hi @Elena_Kalina, apologies, the financial year in the UK starts in April and ends in March. I went over the numbers again, and I'm still getting the same as my original post.

I should have mentioned that if the end date is blank, then the tenancy is still active, and I would need the blank to show as today's date.

For example, in FY2024, Tenant number 2 should have been counted once in each quarter as they were active in each quarter.

I hope this helps to clarify my post. Thanks for your help!

[MFelix]

Hi @RichOB ,

How do you consider a rent that finish before the end of the quarter is it active in the quarter or not?

In this calculation I have made the consideration that if end during the quarter is active and the values I get are 4, 6,8,6.

If I do not consider them to be active at the end of the quarter then the numbers are different:

The first one does not match your calculation

You need to have a calendar table has I refered with the fiscal quarter and year and then you can add one of two measures:

Leases in Period  with ended leases= 
COUNTROWS (
    FILTER (
        Rent,
        (
            Rent[Start_Date] <= MAX ( 'Calendar'[Date] )
                && Rent[End_Date] >= MIN ( 'Calendar'[Date] )
        )
            || (
                Rent[Start_Date] <= MAX ( 'Calendar'[Date] )
                    && Rent[End_Date] = BLANK ()
            )
    )
)

Leases in Period  without ended leases = 
COUNTROWS (
    FILTER (
        Rent,
        (
            Rent[Start_Date] <= MAX ( 'Calendar'[Date] )
                && Rent[End_Date] >= MAX ( 'Calendar'[Date] )
        )
            || (
                Rent[Start_Date] <= MAX ( 'Calendar'[Date] )
                    && Rent[End_Date] = BLANK ()
            )
    )
) 

Please see file attach.

Be aware that the calendar table is poorly build I just did some basic columns to make the example.

[MFelix]

Hi @RichOB ,

Add a calendar table to your model and then try the following measure:

Leases in Period = 
COUNTROWS (
    FILTER (
        Rent,
        (
            Rent[Start_Date] <= MAX ( 'Calendar'[Date] )
                && Rent[End_Date] >= MIN ( 'Calendar'[Date] )
        )
            || (
                Rent[Start_Date] <= MAX ( 'Calendar'[Date] )
                    && Rent[End_Date] = BLANK ()
            )
    )
)