Forum Discussion

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

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

Community 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

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
Community 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
  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
    Community 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?