Recursively calculate diff between dates for customer contracts

[Anonymous]

Here is a dataset of customer contracts where a customer can have more than one contract, typically yearly. For example, Bruno Lage has 2 one year contracts, one starting on 05/02/20 and the other on 07/10/2021. 

I'm trying to calculate the field named 'Gap' (Gap = Start Date - last Enddate). This field should identifiy the time between contracts for each customer in days, so if its a new contract then it should be 0 or if a customer has immediately renwed their contract then it should also be 0 (e.g their contracts ends on 22/02/2022 and they have a new one starting on the same date).

Basically, I think we need to order the data according to start date and then recursively find the difference between dates (Start Date - last Enddate).

How can I calculate this coloum/measure with DAX?

Any help is much appreciated!

[tamerj1 · Accepted solution]

Hi @Anonymous 
Here is the sample file with solution https://www.dropbox.com/t/pbCv6QBPPQp0LhAy
The table looks like this

You need to create an order column:

Order = 
VAR CurrentID =
    Contracts[Id]
VAR CurrentStartDate =
    Contracts[StartDate]
VAR CurrentIdTable =
    FILTER (
        Contracts,
        Contracts[Id] = CurrentID
    )
VAR Result =
    RANKX (
        CurrentIdTable,
        Contracts[StartDate], ,
        ASC
    )
RETURN
    Result 

Then the difference column

Gap (days) = 
VAR PreviuosEndDate =
    LOOKUPVALUE (
        Contracts[EndDate],
        Contracts[Id],
        Contracts[Id],
        Contracts[Order],
        Contracts[Order] - 1
    )
VAR Difference =
    DATEDIFF ( PreviuosEndDate, Contracts[StartDate], DAY )
VAR Result =
    IF (
        ISBLANK ( PreviuosEndDate ),
        0,
        Difference
    )
RETURN
    Result

Please let me know if this satistfies your requirement. Thank you

[Anonymous]

95% of this is exactly what i was looking for, thank you, much appreciated!   

The only error i got was from the final ISBLANK() check, it returned the following =  "A table of multiple values was supplied where a single value was expected."

[tamerj1]

Technically, it should not be possible.
LOOKUPVALUE retruns a value not a table. And if it recieves multiple values it returns a blank. There is not a single table in the whole formula! Can you please explain further. I guess you have blanks in the start date? As duplicate start dates shall not be possible under the same name?

[Anonymous]

It looks like duplicate start dates ARE possible for the same customer. So a customer can have more than 1 contract starting on the same date. Would that cause this issue?  
Also, Not all contracts are 1 year contracts, some are 2 or 3 years long.
Below is an exmaple of a customer whose data looks a bit unusual and the Result it returns for Gap.

[tamerj1]

You are right. If you have duplicate start date then the value argument inside LOOKUPVALUE will no longer be a scalar value but a table of multiple values (depending on how many duplicate start dates per customer) and this will result in an error. But in this case the whole calculation does not make sense because we cannot tell what subtract from what!! For example can you tell which numbers to you want to achieve in the above example?

[Whitewater100]

Hello:

I'm not sure I totally understand but I beleive this will get you close -you can change the IF Statement:

Three Calc Columns. Please see below. I hope this gets you going in the right direction!

Max EDate =

MAXX(

FILTER(

'Table',

'Table'[ID] = EARLIER('Table'[ID])),

'Table'[End Date])

Min Date =

MINX(

FILTER(

'Table',

'Table'[ID] = EARLIER('Table'[ID])),

'Table'[Start Date])

Answer = IF('Table'[END Date] < TODAY() -365,0,

INT('Table'[Max EDate] - 'Table'[Min Date] ))