how do you check the value of a column from previous rows

[Ray_Brosius]

I have a challenge that I think is easy.. but I can't remember how to do it.. 

Below is a screenshot of sample data..  This is a table of "snapshot" data  for each object we capture Monthly snapshots into one table of some attributes.. like close date and amount.

I want to check if the Close date has changed and if the Amount has changed. ( I'll want to know by how much but for now just looking to get the M CODE logic to be able to check the previous snapshot for each object for changes to Close Date and Amount.. 

[Anonymous]

You don't even need to make a copy of the table.  You can choose the same table as your right table in the merge dialogue, and merge the table to itself.

--Nate

[Anonymous]

The least painful way is to add two index columns, one starting at 1 and o e starting at 0. Then use the Merge function in the GUI. Choose the index starting at 1 from your first table, and the one starting at 0 from the right table. Now your values will line up left to right, and you can do each [Columnx] = [Columny] comparisons.

--Nate

[Ray_Brosius]

@Anonymous    I seem to remember this..   I will play with it.. but a clarification question..

1) I need to make a copy of the table and then have one index on both tables, one starting at 0 and one starting at 1.  Then Merge the two tables correct? 

[v-kelly-msft · Accepted solution]

Hi  @Ray_Brosius ,

Below is a blog telling how to achieve it in 2 ways:

https://exceltown.com/en/tutorials/power-bi/powerbi-com-and-power-bi-desktop/power-bi-data-sources/p...

I made a sample .pbix file,pls see attached.

Best Regards,
Kelly

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

[Greg_Deckler]

@Ray_Brosius 

Whoops, missed that this was Power Query, let me try to remember that one. Below is DAX. Can you paste that example data as text?

See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous