Forum Discussion
How to get below result
Hi,
Requirment : If user select next months like below screenshot and
If property is having both 'current' and 'pending' lease status then we need to get 'Current'
If property is having only 'pending' lease status then we need to get 'pending' based on month selection.
Used below logic
AllMnts_Count of Propertiess = VAR _minD = CALCULATE(MIN(Datecalendar[TheDate])) VAR _maxD = CALCULATE(MAX(Datecalendar[TheDate])) VAR _Day = IF(MONTH(_maxD) IN {1,3,5,7,8,10,12}, 31, IF(MONTH(_maxD) IN {4,6,9,11}, 30, 28) ) VAR _minDate = DATE(YEAR(_minD),MONTH(_minD),1) VAR _maxDate = DATE(YEAR(_maxD),MONTH(_maxD),_Day) RETURN CALCULATE(DISTINCTCOUNT(LEASE_EXPIRATION[Property_ID__c]), NOT LEASE_EXPIRATION[Yardi_Lease_Status__c] in { "Canceled","Past"} && LEASE_EXPIRATION[Lease_Sign_Date__c] <> BLANK() && LEASE_EXPIRATION[MTM_Start_Date__c] = BLANK() , FILTER(LEASE_EXPIRATION, LEASE_EXPIRATION[Lease_End_Date__c] >= _minDate && LEASE_EXPIRATION[Lease_End_Date__c] <= _maxDate) ) |
I will provide detailed data for requirment.
Please look below detailed data.
| Property ID | Market | Lease EndDate | MTM StartDate | Renewal Date | Lease Status | Lease_Sign_Date__c | MTM_End_Date__c | Properties |
| ATL00001 | Atlanta | 1/4/2024 0:00 | 11/6/2023 0:00 | Current | 12/29/2021 0:00 | 1 | ||
| ATL00001 | Atlanta | 1/5/2026 0:00 | Pending | 12/29/2021 0:00 | 1 | |||
| ATL00033 | Atlanta | 2/17/2024 0:00 | Current | 2/12/2022 0:00 | 1 | |||
| ATL00033 | Atlanta | 2/17/2025 0:00 | Pending | 2/12/2022 0:00 | 1 | |||
| ATL00430 | Atlanta | 4/1/2025 0:00 | Pending | 12/23/2023 0:00 | 1 | |||
| CLT00230 | Charlotte | 1/26/2024 0:00 | 11/15/2023 0:00 | Current | 1/18/2023 0:00 | 1 | ||
| CLT00230 | Charlotte | 4/26/2025 0:00 | Pending | 1/18/2023 0:00 | 1 | |||
| SAT00220 | San Antonio | 3/11/2025 0:00 | Current | 11/30/2023 0:00 | 1 |
Expected Result is heighlethed in yellow color.
1 Reply
Your expected result is not legible. Please provide a larger screenshot.
