Forum Discussion

TriveniN's avatar
TriveniN
Icon for Helper II rankHelper II
2 years ago

How to get below result

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

 

 

Need Help

2 Replies

    • TriveniN's avatar
      TriveniN
      Icon for Helper II rankHelper II
      2 years ago

       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 IDMarketLease EndDateMTM StartDateRenewal DateLease StatusLease_Sign_Date__cMTM_End_Date__cProperties
      ATL00001Atlanta1/4/2024 0:00 11/6/2023 0:00Current12/29/2021 0:00 1
      ATL00001Atlanta1/5/2026 0:00  Pending12/29/2021 0:00 1
      ATL00033Atlanta2/17/2024 0:00  Current2/12/2022 0:00 1
      ATL00033Atlanta2/17/2025 0:00  Pending2/12/2022 0:00 1
      ATL00430Atlanta4/1/2025 0:00  Pending12/23/2023 0:00 1
      CLT00230Charlotte1/26/2024 0:00 11/15/2023 0:00Current1/18/2023 0:00 1
      CLT00230Charlotte4/26/2025 0:00  Pending1/18/2023 0:00 1
      SAT00220San Antonio3/11/2025 0:00  Current11/30/2023 0:00 1

       

      Expected Result is heighlethed in yellow color.