Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Substract 2 date from differente row relate by a key

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 numberdatesale stage
14/5/2019identified
14/1/2020qualified
14/7/2020proposal

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

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    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

3 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

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

      Thank you !!! it works perfectly

      • v-lili6-msft's avatar
        v-lili6-msft
        Community Support
        7 years ago

        hi, Anonymous 

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

         

        Best Regards,

        Lin