cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TimRensen
New Member

Find MAX Date from previous records with the same ID

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.


 

3 REPLIES 3
stevedep
Memorable Member
Memorable Member

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. 

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

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. 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors