Previous datetime for streaming dataset

[BN_04]

Hi,
I have streaming dataset. Requirement is to show live data. To perfrom some calculations I connect this dataset to powerbi Desktop and adding measures as per need. Now I am trying to create a measure which will give me perivous value of datetime. I want time diffrence of two entity. Please check below image.

In this case I want new column with perivous datetime. I want to calculate time diffrence of non Blank ESN value to next stopper_down_time_Stamp.

I tried one measure-

CALCULATE(index(-2,ALLSELECTED('live-table'[HosurDateTime]),ORDERBY('live-table'[HosurDateTime],ASC)),KEEPFILTERS('live-table'))

But this is giving me only one value for latest record. not complete column values.

Please help me out.

Thanks.

[Dangar332 · Accepted solution]

Hi, @BN_04 

Use offset function instead of index function

Because Index only give static value and offset give daynamic value.

add column which give you previous value

pre = OFFSET(-1,ALLSELECTED(previous[datetime]),ORDERBY(previous[datetime],ASC))

here you can change (-1 to -2) if you want previous value of same id i take only previous value without same id

create mesure of difference bw two dates

diffinminutes =IF(VALUES(previous[pre])=BLANK(),0,DATEDIFF(max(previous[pre]),max(previous[datetime]),MINUTE))

here you can calculate difference as second, hour,day as your prefrence only replace minute to(second, hour,day) 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly. Appreciate your kudos