Find MAX Date from previous records with the same ID

[TimRensen]

Hello everyone,

I could need some help wiht the DAX formular for a new calculated table column.

My goal is to get the last datetime for a specific filter within the same table. My query is working fine with a small amount of data but it quickly runs into performance issues (OOM & Time).

For a better understanding of my goal you will find example data, the expected output and my current query below:

Given Table:

TaskID		Start DateTime		End DateTime
...		...		...
222		19.02.2020 06:54:47		19.02.2020 06:57:09
222		19.02.2020 06:57:09		19.02.2020 07:01:12
333		19.02.2020 06:58:52		19.02.2020 07:02:00
222		19.02.2020 07:05:15		19.02.2020 07:10:46
444		19.02.2020 07:12:41		19.02.2020 08:11:57
222		19.02.2020 07:13:33		19.02.2020 07:17:14
444		19.02.2020 08:11:59		19.02.2020 08:16:42
...		...		...

Expected Output for the new column "Previous EndTime":

TaskID		Start DateTime		End DateTime		Previous End DateTime
...		...		...		...
222		19.02.2020 06:54:47		19.02.2020 06:57:09		19.02.2020 06:53:23
222		19.02.2020 06:57:09		19.02.2020 07:01:12		19.02.2020 06:57:09
333		19.02.2020 06:58:52		19.02.2020 11:23:48		18.02.2020 17:52:45
222		19.02.2020 07:05:15		19.02.2020 07:10:46		19.02.2020 07:01:12
444		19.02.2020 07:12:41		19.02.2020 08:11:57		19.02.2020 06:32:11
222		19.02.2020 07:13:33		19.02.2020 07:17:14		19.02.2020 07:10:46
444		19.02.2020 08:11:59		19.02.2020 08:16:42		19.02.2020 08:11:57
...		...		...		...

Currently in use DAX:
Previous End DateTime = CALCULATE(
     MAX('Table'[End DateTime]),
     FILTER(
          'Table',
          'Table'[TaskID] = EARLIER(TaskID)
          && 'Table'[End DateTime] <= EARLIER('Table'[Start DateTime])
     )
)

Additional Information:
The "Previous DateTime" has to be from the same TaskID. The "Start DateTime" does not have to be equivilant to the "Previous End DateTime". The gap between "Start DateTime" and "Previous End DateTime" can be 0sec but it could also be a gap of more than a month.

For the column "Previous EndTime" i can't use a measure because it shouldn't be affected by any additional filters and I have to do further calculations (e.g. difference between "Previous End DateTime" and "Start DateTime").

I'm pretty new to PowerBI and DAX, so I would be happy and thankful for any information, help, advices or a new perspectiv to solve this problem.

[stevedep]

Hi,

Perhaps with a variable, it performs better?

https://www.sqlbi.com/articles/variables-in-dax/ , see bottom of article for a simular example.

Kind regards, Steve. 

[TimRensen]

Unfortunately I already tried that with the same issues that I'm running OOM and that it calculates for ages. 

[stevedep]

You can try to build it in Power M. I just posted an answer in the Power Query area that does exactly this.

https://community.powerbi.com/t5/Power-Query/Equivalent-DAX-Formula-in-Power-query/m-p/1674977/highl... 

Kind regards, Steve.