Forum Discussion

Yamabushi's avatar
Yamabushi
Helper I
7 years ago
Solved

Dynamic DATEDIFF problem

Hello,   I am trying to calculate the date difference between various rows in minutes as follows: I need to know the date difference between transactions for each person individually. If two o...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Yamabushi ,

     

    To use this formula to create a calcualted column.

     

    Column 2 = 
VAR ind = Table1[Index] - 1
VAR pre =
    CALCULATE (
        MAX ( 'Table1'[Date and time of transaction] ),
        FILTER ( Table1, Table1[Index] = ind )
    )
RETURN
    DATEDIFF ( pre, 'Table1'[Date and time of transaction], MINUTE )

     

    Regards,

    Frank

Greg_Deckler's avatar
Greg_Deckler
Community Champion
7 years ago

I think you are going to need to use an Index to solve this. So you will need that and will want to check how many rows you have of the same sample and date/time. If you have multiple, then check your Index. If your current row is the MIN index, then >, otherwise, >=. If you do not have multiple, then >.

  • Yamabushi's avatar
    Yamabushi
    Helper I
    7 years ago

    Hi Greg_Deckler,

     

    Thank you for you reply! Would you care to elaborate a bit more in depth? I'm not sure I quite understand, I'm not really an expert in this stuff.

     

    Thank you!

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

      Hi Yamabushi,

       

      Firstly, I insert an index column in Power query. after that, I create a calculated column as below. If I misunderstood your requirement, kindly share your excepted result to me.

       

       

      Column 2 =
DATEDIFF (
    CALCULATE (
        MAX ( 'Table1'[Date and time of transaction] ),
        FILTER (
            Table1,
            'Table1'[Transaction type] = EARLIER ( Table1[Transaction type] )
                && Table1[Index] < EARLIER ( Table1[Index] )
        )
    ),
    'Table1'[Date and time of transaction],
    MINUTE
)

       

      Regards,

      Frank

      • Yamabushi's avatar
        Yamabushi
        Helper I
        7 years ago

        Hi v-frfei-msft,

         

        If I have 2 transactions at the same time, I would like the first transaction to show the difference from the previous transaction (as it was at a different time). The next transaction (which is at the same time as the previous transaction), should show 0.

         

        So the solution is not quite what I'm looking for yet. I want the second row to show 1535 and the third row to show 0.

         

        Hope this is not too complicated.

         

        Best regards