Forum Discussion
Dynamic DATEDIFF problem
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
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!
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
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
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