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

Compare values from previous rows

Hello guys! I have a problem...hope you can help me.

 

We have a process in which every step is inserted in the database. The goal is to know how many days the process stuck in a particular status, by comparing the ID number of the application. Like this:

 

forum 1.PNG

 

I need the days column in power bi, because I need to calculate an average per process step.

I've tried several solutions that I found on this forum (trying editing in M, adding index, loockup values, etc) but I cannot get the result I need. Any help would be appreciated

 

Thank you!

 

Here's a data sample if needed:

 

Application IDApplication StatusCreated DateDays
281Default1/6/20151
281Withdrawn By Candidate1/7/2015880
301Default1/8/201511
301CV Review1/19/201537
301DQ CV Review2/25/2015831
321Default1/7/201512
321CV Review1/19/201529
321DQ CV Review2/17/2015839
341Default1/7/20150
341CV Review1/7/2015880
362Default1/7/201512
362CV Review1/19/201529
362DQ CV Review2/17/2015839
381Default1/9/201510
381CV Review1/19/201537
381DQ CV Review2/25/2015831
401Default1/9/201510
401CV Review1/19/201518
401DQ Other Reasons2/6/2015850
421Default1/12/20157
421CV Review1/19/2015868
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Compare values from previous rows

Solution in Power Query (M):

 

let
    Source = Table1,
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index.1"},#"Added Index1",{"Index"},"Next",JoinKind.LeftOuter),
    #"Expanded Next" = Table.ExpandTableColumn(#"Merged Queries", "Next", {"Application_ID", "Created_Date"}, {"Next.Application_ID", "Next.Created_Date"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Next", "Days", each Duration.Days(if [Next.Application_ID] = [Application_ID] then [Next.Created_Date] - [Created_Date] else DateTime.Date(DateTime.LocalNow())-[Created_Date]), Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Index.1", "Next.Application_ID", "Next.Created_Date"})
in
    #"Removed Columns"
Specializing in Power Query Formula Language (M)

View solution in original post

6 REPLIES 6
Highlighted
Super User IV
Super User IV

Re: Compare values from previous rows

So that I understand this, if there is another date for an Application ID, then a status change occurred and you want the difference between this next date and that date. Otherwise you want the difference between that date and today?


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Frequent Visitor

Re: Compare values from previous rows

Thats right. If the date changes, but the application doesn't, a status change occured.

The data is sorted on Application ID, so we can be sure that when the ID changes, the previous row was the last status of the prior ID.

Highlighted
Super User IV
Super User IV

Re: Compare values from previous rows

Well, that looks like a job for EARLIER but to be honest, I'm not that very good with EARLIER.

 

https://msdn.microsoft.com/en-us/library/ee634551.aspx


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Community Champion
Community Champion

Re: Compare values from previous rows

Solution in Power Query (M):

 

let
    Source = Table1,
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index.1"},#"Added Index1",{"Index"},"Next",JoinKind.LeftOuter),
    #"Expanded Next" = Table.ExpandTableColumn(#"Merged Queries", "Next", {"Application_ID", "Created_Date"}, {"Next.Application_ID", "Next.Created_Date"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Next", "Days", each Duration.Days(if [Next.Application_ID] = [Application_ID] then [Next.Created_Date] - [Created_Date] else DateTime.Date(DateTime.LocalNow())-[Created_Date]), Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Index.1", "Next.Application_ID", "Next.Created_Date"})
in
    #"Removed Columns"
Specializing in Power Query Formula Language (M)

View solution in original post

Highlighted
Frequent Visitor

Re: Compare values from previous rows

MarcelBeug, thank you so much!

This is what I needed, I mean, I'm still trying to really understand what you did, but this is amazing!

 

Thanks again 🙂

Highlighted
Community Champion
Community Champion

Re: Compare values from previous rows

In general if you add 2 indices, one 0-based and the other 1-based, and use these to merge the table with itself, you get in the nested tables the values:

  • from the previous row if you merge the 0-Index (first table) with the 1-index (second table)
  • from the next row if you merge the 1-index (first table) with the 0-index (second table)

Notice: if you adjust the generated code from the merge step and adjust "NewColumn" to "Previous" or "Next", then you have clear field names if you expand the required fields from this column and make sure to use the current column name as prefix.

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors