Forum Discussion
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 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
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.
8 Replies
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 () ) ) )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
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!
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.
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.
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.Hi RichOB ,
I hope the suggested solution worked for you. If your issue is resolved, kindly accept the post as a solution — it helps the community identify helpful answers more easily.
Thank you.
Hi RichOB ,
I wanted to follow up to see if you had a chance to review the previous response provided by Community members. I hope it was helpful. If you still have any further questions,feel free to contact us!
Thank you.
