Get Date from previous status

[Martin74]

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 want to get the AlarmTime (add column). For some PBI files this works fine but when there are more records (between 200,000 - 300,000 rows) my memory usage is getting high and stays hig (100%). Normal I use the following DAX and replace some to fit my current table it gets the job well done by max 60,000 rows above the memory usage is high (installed 32GB)

I wonder why the DAX works in most cases but not in a model with more records, I don't know how to fix this tried a lot of different DAX etc. Perhaps for lager models the way I want to get it done is not the right one....

DateTime Start =

IF('NSA Bedrijf'[ActualValue]=0,

CALCULATE (

MAX ( 'NSA Bedrijf'[DateTime End] ),

FILTER (

ALLEXCEPT (

'NSA Bedrijf',

'NSA Bedrijf'[PointName]

),

'NSA Bedrijf'[DateTime End]

< EARLIER('NSA Bedrijf'[DateTime End] )

)

),BLANK())

Example of the lager models with high memory usage:

 

[amitchandak · Accepted solution]

@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]

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