- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: DAX lookup column based on previous id in same...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

ndesousa

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-11-2017
07:51 PM

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.

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

v-shex-msft

Community Support Team

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-19-2017
01:29 AM

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: | |

If this post

For learning resources/Release notes, please visit: | |

4 REPLIES 4

v-shex-msft

Community Support Team

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-12-2017
08:16 PM

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: | |

If this post

For learning resources/Release notes, please visit: | |

ndesousa

Frequent Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-17-2017
09:26 PM

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.

v-shex-msft

Community Support Team

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-19-2017
01:29 AM

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: | |

If this post

For learning resources/Release notes, please visit: | |

Highlighted
##

ndesousa

Frequent Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-10-2017
10:47 PM

Thanks again for your reply.

Apologies for not replying sooner as I have on leave.