cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ndesousa Frequent Visitor
Frequent Visitor

DAX lookup column based on previous id in same table

Hi All,

 

I'm hoping someone can assist with a lookup scenario.

 

I have one table (Image 1) with 2 id columns: op_id, prev_op_id and 2 operational DateTime fields: start_op_dt, end_op_dt.

 

In this image the start_op_dt and end_op_dt are for op_id

 

Image 1

sample 1.jpg

 

 

 

I have to calculate the time difference between the end_op_dt of prev_op_id and start_op_dt of op_id.

 

 

I have tried duplicating the table and linking pre_op_id (duplicated table) to _op_id. Image 2 lists the data of based on duplicating and joing the 2 tables.

 

I then created a DAX to calculate the time difference between end_op_dt and start_op_dt. However each prev_op_id got replicated by the total number of op_ids.

 

sample 2.jpg

 

Is there a way to calculate the time difference within 1 table where op_id where I can search for the end_op_dt for column prev_op_id?

 

Thank you.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: DAX lookup column based on previous id in same table

Hi @ndesousa,

 

You can also try to use below formula to calculate between current id and previous id.

 

Calculate column:

Diff = DATEDIFF(LOOKUPVALUE([end_op_dt],[op_id],[Prev_op_id]),[start_op_dt],SECOND) 

 

BTW, I think you should switch some correct records, I found your previous end date are greater than current start date.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
4 REPLIES 4
Community Support Team
Community Support Team

Re: DAX lookup column based on previous id in same table

Hi @ndesousa,

 

I'd like to suggest you add a calculate column to store the diff value between start_date and end_date, then use it to calculate the difference of current row and previous row.

 

Below are sample formulas, you can refer to they if they suitable for your requirement.

 

calculate columns.

 

Diff

Diff = DATEDIFF([start_op_dt],[end_op_dt],SECOND) 

 

 

Diff between current row and previous row.

Diff C - P = [Diff]-LOOKUPVALUE([Diff],[op_id],[Prev_op_id])

 

If you not want to use calculated column, you can try to use follow measure to calculate the diff between current and previous.

Result = MAX([Diff])-LOOKUPVALUE(Table[Diff],Table[op_id],MAX(Table[Prev_op_id]))

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Highlighted
ndesousa Frequent Visitor
Frequent Visitor

Re: DAX lookup column based on previous id in same table

Hi @v-shex-msft,

 

Thanks for your reply.

 

Unfortunately, I was not getting the answer I was hoping for. I was looking to get the difference between the start_op_date for the op_id and the end_op_dt for the prev_op_id.

 

I modified your formula to derive my value:

 

CALCULATE(
 1440 *
 (SUM(Table1[start_op_dt]) -
  CALCULATE(

        (LOOKUPVALUE(Table1[end_op_dt], Table1[op_id], MAX(Table1[prev_op_id])))
 )))

 

However, this measure gave me the result only at an id level, which is fine, but when I visulaised it at a monthly level, I got highly inflated numbers. I was after average for the month. I tried replacing SUM with AVERAGE, however this didn't work.

 

I finally managed to solve it by doing the following:

 

I duplicated the table twice:

1. Just containing op_id and prev_op_id

2. Duplication of the main table

 

I then joined:

* op_id (table 1) to op_id (table 2)

* prev_op_id (table 2) to op_id (table 3)

 

I then created a calculated column:

CALCULATE(IF(
 OR(ISBLANK(SUM(Table3[end_op_dt])),
  ISBLANK(SUM(Table1[start_op_dt])))
  ,BLANK(),
  1440 * (SUM(Table1[start_op_dt]) - SUM(Table3[end_op_dt]))
 ))

 

Finally I changed the default summarisation from Sum to Average.

 

Thanks again for your help. I appreciate it.

Community Support Team
Community Support Team

Re: DAX lookup column based on previous id in same table

Hi @ndesousa,

 

You can also try to use below formula to calculate between current id and previous id.

 

Calculate column:

Diff = DATEDIFF(LOOKUPVALUE([end_op_dt],[op_id],[Prev_op_id]),[start_op_dt],SECOND) 

 

BTW, I think you should switch some correct records, I found your previous end date are greater than current start date.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
ndesousa Frequent Visitor
Frequent Visitor

Re: DAX lookup column based on previous id in same table

Thanks again for your reply.

 

Apologies for not replying sooner as I have on leave.