How to have a calculate column that compares the difference between the current item and the previously submitted item?

Question

I have a list with the columns Room Number, Temperature, and a column Temperature Change.

What I would like, is for Temperature Change to be a calculated like so:

When the user fills out a new form, they enter the Room Number and Temperature. Then there's a lookup that determines the last item submitted in the list with that same Room Number. It then gets that previously submitted list item and calculates Temperature Change from the difference between the current item's Temperature and the last item's temperature.

Resulting in something that looks like this:

Room Number Temperature Temperature Change  Date Submitted
A           20          blank               1/1/2015
B           22          blank               1/1/2015
A           25          5                   2/1/2015
B           24          2                   2/1/2015

Is there anyway to do this using Lookup and Calculated columns?

Answer 1

Note:

in June 2017, Microsoft disabled the use of JavaScript in a Calculated Column

That means given answers may not apply for newer SharePoint versions

For long explanation and work arounds see:
June 13th 2017 Microsoft blocked handling HTML markup in SharePoint calculated fields - how to get the same functionality back

---

Original answer:

If you want the delta values displayed in a View like this:

• You must create a View that Groups by Room and sorts by Date

• A Calculated Column 'ChangeMonitor' with Formula
  set to datatype=Number to output HTML/JS
  can then use JavaScript code to calculate, display and color the deltas

Copy/paste this Formula to your Calculated Column Formula:

    ="<img src=/_layouts/images/blank.gif onload=""{_spYield(function(){"
    &" var delta=0,TR=GetAncestor(this,'TR'),"
    &" ID=TR.id.split(',')[1],"
    &" ctxRows=ctx.ListData.Row,"
    &" n=getItemIdxByID(ctxRows,ID);"
    &" if(GetAncestor(TR,'TABLE').rows[TR.rowIndex-1].hasAttribute('id')){"
    &"  delta=ctxRows[n].Temperature - ctxRows[n-1].Temperature;"
    &"  this.parentNode.innerHTML=(delta>0?'+':'')+delta;"
    &"  TR.style.backgroundColor=delta>0?'pink':'lightblue';"
    &"  }"
    &"}.bind(this),20);}"">"

Because this code is executed inline and not all SharePoint libraries have loaded yet, the execution is (brute force for 20 cycles) delayed with the (SharePoint) _spYield function.
Once SharePoints core.js is loaded we can use all those goodie functions SharePoint gives you for free.
Because the View is grouped and GroupHeaders are also TR tablerows some checking is required to determine if a Row is not the first row in a Group.

I haven't tested this but I think all this works in SP2010 as well

In SP2013 you can do it in CSR - Client Side Rendering, but requires about 20+ lines of boilerplate code, separate CSR file and JSlink for every View you want to use it in.

Recording Change

I have not used the Change column, the View Displays the Delta, it does not record it.

If you need to record the delta you need to add JSOM/REST code to update the Change field

ICC

With 2013 CSR - Client Side Rendering

With CSR you have easier control over the display of fields and can achieve:

I can not share the CSR code, client will sue me to bits

You have to loop though all items upfront, record deltas
Then display a bar for each item

J1 J5 ICSR