Calculate days difference between two lease contracts for same unit

[ChandraDXB]

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 Name	Start      Date	End Date	Days	Remark
PROP03-1002	000767-1	AZRIS	31-Oct-17	30-Oct-18	868	3/16/2021-10/30/2018
PROP03-1002	001607-1	ALPHA	16-Mar-21	15-Apr-22	481	8/9/2023-4/15/2022
PROP03-1002	L02195	Salu	9-Aug-23	8-Sep-24	0	Unit still occupied, so no renewal
PROP03-1111	000187-1	Explorer	4-Sep-17	3-Sep-18	135	1/16/2019-9/3/2018
PROP03-1111	001023-1	Vision	16-Jan-19	15-Feb-20	0	Day difference is 1 day but same tenant so 0 day
PROP03-1111	001023-2	Vision	16-Feb-20	15-Feb-21	129
PROP03-1111	L00079	Alliance	24-Jun-21	23-Jul-22	0	Day difference is 1 day but same tenant so 0 day
PROP03-1111	L01219	Alliance	24-Jul-22	28-Jul-22	12
PROP03-1111	L01241	LEGAL	9-Aug-22	8-Aug-23	98
PROP03-1111	L02654	TRAVEL	14-Nov-23	13-Dec-24	0	Unit still occupied, so no renewal
PROP03-3616	000243-1	Mohammad	9-May-17	8-May-18	28
PROP03-3616	000894-1	English	5-Jun-18	11-Sep-19	4
PROP03-3616	001112-1	Artificial	15-Sep-19	8-Jun-20	0	Day difference is 1 day but same tenant so 0 day
PROP03-3616	001112-2	Artificial	9-Jun-20	8-Jun-21	36
PROP03-3616	L00087	Omar	14-Jul-21	14-Jul-21	0	Day difference is 1 day but same tenant so 0 day
PROP03-3616	L00831	Omar	15-Jul-21	13-Aug-22	1
PROP03-3616	L01832	BLUE	14-Aug-22	13-Aug-23	0	Day difference is 1 day but same tenant so 0 day
PROP03-3616	L02982	BLUE	14-Aug-23	13-Aug-24	0	Unit still occupied, so no renewal

Thanks for your suggestion,

Chandra

[Fowmy · Accepted solution]

@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]

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

[ChandraDXB]

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.