Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
Requirment
If property is having both 'current' and 'pending' lease status then we need to get 'Current'
If property is having both 'pending' lease status then we need to get 'pending'.
below is the sample data
Expected Output ismentioned in Heilghted values below
Hi,
Thanks for quick response.
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.
User | Count |
---|---|
98 | |
89 | |
82 | |
70 | |
67 |
User | Count |
---|---|
114 | |
104 | |
101 | |
72 | |
65 |