Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

datediff in columns with a equal id

 

I'm trying to calculate the date difference between rows that have the same issueid in the case we have more than one entry. I tried to use a method that I searched here but I could not make it work. Can someone help? If it was plain code it would be easy. 🙂

 

 

image.png

11 REPLIES 11
Sean
Community Champion
Community Champion

What exactly is the desired outcome? You have 8 rows with 14318 issueid what should be the result?

Anonymous
Not applicable

The ideia is to get the diff between each row with that id. This table in fact is a status change history table. So what I need is to get the time an issueid remain in that state.

 

Using issueid 14318 it would be something like

 

20 sec

9 sec

3 sec

5 sec

1m52sec

4 sec

empty

 

Hi @Anonymous,

 

You could achieve this requirement by the calculated columns below.

Index = RANKX(FILTER(Table1,Table1[ISSUEID]=EARLIER(Table1[ISSUEID])),Table1[Create],,ASC)
PreviousTime = LOOKUPVALUE(Table1[Create],Table1[ISSUEID],Table1[ISSUEID],Table1[Index],Table1[Index]-1)
datediff = DATEDIFF(Table1[PreviousTime],Table1[Create],SECOND)

Capture.PNG

 

Regards,

Charlie Liao

 

Anonymous
Not applicable

Thanks @v-caliao-msft

 

In fact now I have the diff correctly, but strugling to the fact that If I display the issue by the state it entered the time is from the last state and not the one it entered..

 

I have to figure how to match the state with is duration

Anonymous
Not applicable

 

image.png

 

Hello again @v-caliao-msft and @MarcelBeug

 

I'm still strugling with this one because I can not manage to display the current (not closed State).

As you can see in the above image I manage to use the formulas provided in the topic and create an index based on start date for each status.
The problem is the end date for the status that is displayed on the rigth but not for the first status. How do I get the end date in the correct status and no end date in the running status (in the image with would be (scoping pipeline)

Thanks

 

In the solution I provided, data from the current row was merged with data from the previous row.

 

Now it looks like you want to merge data from the current row with the data from the next row.

 

You can adjust the step in which the tables are merged: instead of merging the table on Index.0 first and Index.1 next, you need to merge the table on Index.1 first and Index.0 next.

 

 

Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

Ok. I will try.

Take a look at this thread.

 

In PowerQuery, "duration" is pretty much equivalent with "datediff".

Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

Hello Again @MarcelBeug

 

I'm struggling with the index on date time, because I already have that one in the correct form. So i'm not quite understanding the need for that index.

 

Can you help me on that one?

 

Thanks

In the link I provided, you can find a step by step explanation in my post directly under "All Replies".

 

In general, when you need to calculate something based on data that is on different rows, a very efficient way of doing that is to get all required data on the same row,

So first you sort your data in such a way that the data you need are on subsequent rows, so in your case when you sort on issueid and created, you need the timestamp in the current row and the timestamp in the previous row (and you also need the issueid from both rows to verify if it is still the same issueid).

Next steps, in order to get the data on the some row, you add an index starting with 0 and an index starting with 1, then you merge the table with itself (giving you a "NewColumn" with nested tables), rename this column to "Previous", expand the column with the columns you need (so "issueid" and "created"), keeping the column name as prefix and then you can add a column in which you subtract "Previous.created"  from "created" if "Previous.issueid"  = "issueid" (else null).

 

You can watch the first minute of this video in which I did exactly the same for another question.

Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

Thanks.

 

Looking into it, but it will be tricky for me since I do not know much of powerquery.

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors