Forum Discussion

ChandraDXB's avatar
ChandraDXB
Frequent Visitor
2 years ago
Solved

Calculate days difference between two lease contracts for same unit

Hi All,

 

I have to calculate days required to fill property vacancy. This is based on the expiry of the existing contarct and start date of a new contract for same unit. Once I have this information, then I can calculate average days required to fill vacancy property wise or at portfolio level or year wise.  Days can be calculated either in Power query or using DAX.

 

 

Unit No             Contract No    Customer NameStart      DateEnd Date      Days                     Remark
PROP03-1002000767-1AZRIS31-Oct-1730-Oct-188683/16/2021-10/30/2018
PROP03-1002001607-1ALPHA16-Mar-2115-Apr-224818/9/2023-4/15/2022
PROP03-1002L02195Salu9-Aug-238-Sep-240Unit still occupied, so no renewal
PROP03-1111000187-1Explorer4-Sep-173-Sep-181351/16/2019-9/3/2018
PROP03-1111001023-1Vision16-Jan-1915-Feb-200Day difference is 1 day but same tenant so 0 day
PROP03-1111001023-2Vision16-Feb-2015-Feb-21129 
PROP03-1111L00079Alliance24-Jun-2123-Jul-220Day difference is 1 day but same tenant so 0 day
PROP03-1111L01219Alliance24-Jul-2228-Jul-2212 
PROP03-1111L01241LEGAL 9-Aug-228-Aug-2398 
PROP03-1111L02654TRAVEL14-Nov-2313-Dec-240Unit still occupied, so no renewal
PROP03-3616000243-1Mohammad9-May-178-May-1828 
PROP03-3616000894-1English5-Jun-1811-Sep-194 
PROP03-3616001112-1Artificial15-Sep-198-Jun-200Day difference is 1 day but same tenant so 0 day
PROP03-3616001112-2Artificial9-Jun-208-Jun-2136 
PROP03-3616L00087Omar14-Jul-2114-Jul-210Day difference is 1 day but same tenant so 0 day
PROP03-3616L00831Omar15-Jul-2113-Aug-221 
PROP03-3616L01832BLUE 14-Aug-2213-Aug-230Day difference is 1 day but same tenant so 0 day
PROP03-3616L02982BLUE 14-Aug-2313-Aug-240Unit still occupied, so no renewal

 

Thanks for your suggestion,

 

Chandra

"DAX"
DAX&Power Query
  • Fowmy's avatar
    Fowmy
    2 years ago

    ChandraDXB 

    Add the following calculated column to your table:

     

    Days Difference = 
VAR __NextDate = 
 MAXX(
    OFFSET( 1 ,ORDERBY( Table01[Unit No], ASC, Table01[Start Date], ASC ) , PARTITIONBY(Table01[Unit No]) ),
    Table01[Start Date] 
 )
VAR __EndDate =  Table01[End Date] 
VAR __CurrentTenant = Table01[Customer Name]
VAR __NextTenant = MAXX( FILTER( Table01 , Table01[Unit No] = EARLIER(Table01[Unit No]) && Table01[Start Date] = __NextDate ) , Table01[Customer Name] )
VAR __Result = 
    IF( 
        NOT ISBLANK(__NextDate) , 
        IF( __CurrentTenant = __NextTenant , 0 , 
            INT( __NextDate - __EndDate) 
        )
    )+0
RETURN
    __Result

     

     

3 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    ChandraDXB 

    Add the following calculated column to your table:

     

    Days Difference = 
VAR __NextDate = 
 MAXX(
    OFFSET( 1 ,ORDERBY( Table01[Unit No], ASC, Table01[Start Date], ASC ) , PARTITIONBY(Table01[Unit No]) ),
    Table01[Start Date] 
 )
VAR __EndDate =  Table01[End Date] 
VAR __CurrentTenant = Table01[Customer Name]
VAR __NextTenant = MAXX( FILTER( Table01 , Table01[Unit No] = EARLIER(Table01[Unit No]) && Table01[Start Date] = __NextDate ) , Table01[Customer Name] )
VAR __Result = 
    IF( 
        NOT ISBLANK(__NextDate) , 
        IF( __CurrentTenant = __NextTenant , 0 , 
            INT( __NextDate - __EndDate) 
        )
    )+0
RETURN
    __Result

     

     

    • ChandraDXB's avatar
      ChandraDXB
      Frequent Visitor
      2 years ago

      Thanks a lot Fowmy. Best wishes for New year. 

       

      1. If I need to add customer status as if there is a new customer first time for that specific unit , then put "New" else if same customer has renewed the contarct then "Renewal" otherwise "Vacant" as default.

      2. If for same unit, how many times we have got new customer - to get this info, if there is new customer first time then 0 and for every subsequent new customers, put 1. if same customer has renewed the contarct, then put 0. 

      The two scenarios have been provided in the below table.

       

       

      Thanks in advance.

       

    • ChandraDXB's avatar
      ChandraDXB
      Frequent Visitor
      2 years ago

      Dear Fowmy,

       

      Appreciate if you can share the bi file as I am having diffculty in running the DAX function.

       

      DaysDifference =
       VAR _NextDate =
       MAXX(
          OFFSET(1, ORDERBY (LeasingData[Property Unit], ASC, LeasingData[StartDate],ASC), PARTITIONBY(LeasingData[Property Unit])),
          LeasingData[StartDate]
       )
       VAR _EndDate = LeasingData[EndDate]
       VAR _CurrentTenant = LeasingData[CustomerID]
       VAR _NextTennat = MAXX(FILTER(LeasingData, LeasingData[Property Unit] = EARLIER(LeasingData[Property Unit]) && LeasingData[StartDate] = _NextDate), LeasingData[CustomerID])
       VAR _Result =
          IF(
                 NOT ISBLANK(_NextDate),
                  if (_CurrentTenant = _NextTennat, 0,
                      INT(_NextDate - _EndDate)
                  )
          )+0
          RETURN
          _Result