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

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

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

      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'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.