Substract 2 date from differente row relate by a key

[Anonymous]

Hi i want to substract 2 date relate by a opportunity number(key) but that are in differente row but i dont know if it possible to it and how. For example

account number	date	sale stage
1	4/5/2019	identified
1	4/1/2020	qualified
1	4/7/2020	proposal

I wonder is there a way to get the time difference between when the sale stage = identified and when the sale stage = qualified for account number 1.

Thank you

[v-lili6-msft · Accepted solution]

HI, @Anonymous 

Just use EARLIER in this formula to create a new column

time difference = 
VAR _identified =
    CALCULATE (
        MAX ( 'Table'[date] ),
        FILTER (
            'Table',
            [account number] = EARLIER ( [account number] )
                && [sale stage] = "identified"
        )
    )
VAR _qualified =
    CALCULATE (
        MAX ( 'Table'[date] ),
        FILTER (
            'Table',
            [account number] = EARLIER ( [account number] )
                && [sale stage] = "qualified"
        )
    )
RETURN
    DATEDIFF ( _identified, _qualified, DAY )

or

time difference 2 = 
VAR _identified =
    CALCULATE (
        MAX ( 'Table'[date] ),
        FILTER (
            'Table',
            [account number] = EARLIER ( [account number] )
                && [sale stage] = "identified"
        )
    )
VAR _qualified =
    CALCULATE (
        MAX ( 'Table'[date] ),
        FILTER (
            'Table',
            [account number] = EARLIER ( [account number] )
                && [sale stage] = "qualified"
        )
    )
RETURN
   IF( [sale stage] = "qualified"||[sale stage] = "identified", DATEDIFF ( _identified, _qualified, DAY ))

Result:

Best Regards,

Lin

[Anonymous]

Thank you !!! it works perfectly

[v-lili6-msft]

hi, @Anonymous 

It's pleasant that your problem has been solved, could you please mark the reply as Answered?

Best Regards,

Lin