Identifying new and renewal lease and mapping of new customer or existing customer

[ChandraDXB]

Dear All and @Fowmy 

I have to calcuate measures for the following two scenarios:

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.

Solution has been provided in below table, this solution needs to be replicated in DAX

Thanks in advance,

Regards,

Chandra

[ChandraDXB]

Thanks a lot. It has really worked very well.

[Fowmy · Accepted solution]

@ChandraDXB 

Here is the code for both the calculated columns:

Customer Status = 
VAR __UnitNo = Table01[Unit No]
VAR __Customer = Table01[Customer Name]
VAR __StartDate = Table01[Start Date]
VAR __T = FILTER( Table01 , Table01[Unit No] = __UnitNo && Table01[Start Date] < __StartDate && Table01[Customer Name] = __Customer)  
VAR __Result = IF( ISEMPTY( __T) , "New" , "Renewal")
RETURN
    __Result

Contract Status = 
VAR __UnitNo = Table01[Unit No]
VAR __StartDate = Table01[Start Date]
VAR __Customer = Table01[Customer Name]
VAR __T = FILTER( Table01 , Table01[Unit No] = __UnitNo && Table01[Start Date] < __StartDate)  
VAR __Result = 
    SWITCH(
        TRUE(),
        ISEMPTY( __T) , 0,
        __Customer IN SELECTCOLUMNS(__T,Table01[Customer Name]), 0,
        1
    )
RETURN
    __Result