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.

     



     



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.