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 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!

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

6 Replies

  • Whitewater100's avatar
    Whitewater100
    Icon for Solution Sage rankSolution Sage
    4 years ago

    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] ))
  • 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?