Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Recursively calculate diff between dates for customer contracts

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...
DAX
Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    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's avatar
Anonymous
Not applicable
4 years ago

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's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

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?