cancel
Showing results for
Did you mean:
ndesousa Regular 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 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. 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

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

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: | |
ndesousa Regular Visitor

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

Hi @v-shex-msft,

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

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 Regular Visitor

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

Apologies for not replying sooner as I have on leave.

Announcements Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge. Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform. Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag. Top Kudoed Authors
Users Online
Currently online: 43 members 957 guests
Recent signins:
• danextian • nsharma • amitchandak • SathyaG35 • caytung09 • mohanyeedhi • johnmelbourne • nsharma • Ngene • troystaylor • mexicobigdata • Bielite • kylerligon • drewsk 