Forum Discussion

Martin74's avatar
Martin74
Helper I
4 years ago
Solved

Get Date from previous status

I am struggling to subtract date from previous record date based on Status column. Below an example of what I'm trying to get. From SQL there is a DateTime, status and point column. For each point I ...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    Martin74 , Try like

     


    maxx(filter( 'NSA Bedrijf' , 'NSA Bedrijf'[PointName] = earlier('NSA Bedrijf'[PointName]) && 'NSA Bedrijf'[DateTime End]< EARLIER('NSA Bedrijf'[DateTime End] ) ),'NSA Bedrijf'[DateTime End])

     

    do it two steps

     

    Rank  =Rankx (filter( 'NSA Bedrijf' , 'NSA Bedrijf'[PointName] = earlier('NSA Bedrijf'[PointName]) ),'NSA Bedrijf'[DateTime End] ,,asc, dense)

     


    maxx(filter( 'NSA Bedrijf' , 'NSA Bedrijf'[PointName] = earlier('NSA Bedrijf'[PointName]) && 'NSA Bedrijf'[Rank  ]= EARLIER('NSA Bedrijf'[Rank ] )  -1 ),'NSA Bedrijf'[DateTime End])

amitchandak's avatar
amitchandak
Super User
4 years ago

Martin74 , Try like

 


maxx(filter( 'NSA Bedrijf' , 'NSA Bedrijf'[PointName] = earlier('NSA Bedrijf'[PointName]) && 'NSA Bedrijf'[DateTime End]< EARLIER('NSA Bedrijf'[DateTime End] ) ),'NSA Bedrijf'[DateTime End])

 

do it two steps

 

Rank  =Rankx (filter( 'NSA Bedrijf' , 'NSA Bedrijf'[PointName] = earlier('NSA Bedrijf'[PointName]) ),'NSA Bedrijf'[DateTime End] ,,asc, dense)

 


maxx(filter( 'NSA Bedrijf' , 'NSA Bedrijf'[PointName] = earlier('NSA Bedrijf'[PointName]) && 'NSA Bedrijf'[Rank  ]= EARLIER('NSA Bedrijf'[Rank ] )  -1 ),'NSA Bedrijf'[DateTime End])

Martin74's avatar
Martin74
Helper I
4 years ago

Hi amitchandak, tried your solution. behalf it's not the complete solution it's certain a lot of help. Customized your solution and now it's working at lighting speed. In one of the added columns I now get the first date and time of the alarming state of a certain point. From now on I can calculate the difference in time between the normal state and alarm state. Further I can count the difference in time when a point is still in a alarm state.

 

So thanks a lot for your solution, I really appreciate your help

 

Kind regards,

 

Martin