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.