Forum Discussion
Datediff for alternate rows
Hello All,
How do it get date diff in sec. It should be group by ModelNr and Station Nr.
Eg: Datediff(17-11-2020 14:46:02,17-11-2020 14:46:11,Sec)
4 Replies
- amitchandak
Super User
harshagraj , Try a column like
datediff(minx(filter(table, [ModelNr] =earlier([ModelNr]) && [Station Nr] =earlier([Station Nr]) && [prestoparrivalC] > earlier([prestoparrivalC])),[prestoparrivalC]),[prestoparrivalC] , second)
- harshagraj
Post Partisan
Hello thankyou so much for the reply. I tried inserting a Measure and I am on Direct Query mode. I am getting below error. I am not getting a column after Earlier. Am i missing something? Please help
- AnonymousNot applicable
Hi harshagraj ,
According to the official document, it may caused by some limitations...
I did it in three ways after grouping ModelNr and Station Nr columns, please try
ModelNrStation = [ModelNr ] & [Station Nr]Measure = DATEDIFF ( CALCULATE ( MAX ( 'Table'[DepositCarrier_C] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[ModelNrStation] = MAX ( 'Table'[ModelNrStation] ) && 'Table'[DepositCarrier_C] < MAX ( 'Table'[DepositCarrier_C] ) ) ), MAX ( 'Table'[PreStopperArrival_C] ), SECOND )ColumnMethod1 = DATEDIFF ( [DepositCarrier_C], MINX ( FILTER ( 'Table', [ModelNr ] = EARLIER ( [ModelNr ] ) && [Station Nr] = EARLIER ( [Station Nr] ) && [PreStopperArrival_C] > EARLIER ( [PreStopperArrival_C] ) ), [PreStopperArrival_C] ), SECOND )ColumnMethod2 = DATEDIFF ( CALCULATE ( MAX ( 'Table'[DepositCarrier_C] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[ModelNrStation] ), [DepositCarrier_C] < EARLIER ( 'Table'[DepositCarrier_C] ) ) ), [PreStopperArrival_C], SECOND )My final output looks like this:
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.Best Regards,
Eyelyn Qin - harshagraj
Post Partisan
Hello Anonymous thanks a lot helping. But unfortunately i cant do column because its live connection. And i tried the measur but i am getting wrong values. Please see the attached.