Forum Discussion

BN_04's avatar
BN_04
Frequent Visitor
2 years ago
Solved

Previous datetime for streaming dataset

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.

"DAX" "Earliest date"
"Need Help"
Streaming Datasets
  • Dangar332's avatar
    Dangar332
    2 years ago

    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

     

1 Reply

  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    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