Forum Discussion

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

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

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

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