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 informati...
"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

     

     

Fowmy's avatar
Fowmy
Icon for Super User rankSuper 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