Forum Discussion

RichOB's avatar
RichOB
Post Partisan
1 year ago
Solved

Calculating active figures

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

    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.

     



     



Elena_Kalina's avatar
Elena_Kalina
Solution Sage
1 year ago

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's avatar
    RichOB
    Post Partisan
    1 year ago

    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's avatar
      MFelix
      Super User
      1 year ago

      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.