Calculate difference between current and last value

[reporting_neu]

Hi,

I have meter readings and would like to always determine the difference to the last meter reading. For this I have the following tables and values:

meter readings

Date	Meter number	Meter reading
01.01.2022	1000	15
31.01.2022	1000	150
28.02.2022	1000	220
01.01.2022	5004	3582
31.01.2022	5004	6869
etc.

Base data

Area	Meter number	Old meter number	Valid from	Valid to	active
Mainroom	1000	8	01.01.2022	31.12.2022	yes
Mainroom	8		01.01.2021	31.12.2021	no
Airport	5004		01.01.2022	31.12.2022	yes
etc.

The result should look like this:

Date	Meter number	Meter reading	Consumption
01.01.2022	1000	15	0
31.01.2022	1000	150	135
28.02.2022	1000	220	70
01.01.2022	5004	3582	0
31.01.2022	5004	6869	3287
etc.

The tables are connected via the key "Meter number".

I've tried the following without success:

if(previous([meter number]) = [meter number], [meter reading]- previous([meter reading]), 0)

But the condition (previous([meter number]) = [meter number]) already doesn't work.

Who can help me please?

[vinieme12]

As below,  use peek() instead of previous()

Load *
,if( Meternumber=peek(Meternumber),Meterreading-peek(Meterreading),0) as Consumption
Inline [
Date,Meternumber,Meterreading
01.01.2022,1000,15
31.01.2022,1000,150
28.02.2022,1000,220
01.01.2022,5004,3582
31.01.2022,5004,6869
];

[reporting_neu]

Thank you for your formula. It definitely works with your data.

However, the formula doesn't work with my data. And I do not know why. I read an Excel spreadsheet first. The load script looks like this:

NoConcatenate
Meterreading:
LOAD
    Date,
    Meternumber,
    Meterreading,   
   if(Text(Meternumber) <> Peek(Text(Meternumber)), 0, Meterreading - Peek(Meterreading)) as Consumption
      
FROM [lib://FICILITY/Meterreading.xlsx]
(ooxml, embedded labels, table is Meterreading);

What can be the reason? Everything should be right 🤔

[vinieme12 · Accepted solution]

the most importing thing for this to work is that your data must be sorted correctly

If its not sorted correctly it will return incorrect results

However, the formula doesn't work with my data   -  Can you clarify exactly what you mean by doesn't work?

are you getting incorrect values?

or no values at all?

make sure to sort your data correctly

temp:
LOAD
    Date,
    Meternumber,
    Meterreading
      
FROM [lib://FICILITY/Meterreading.xlsx]
(ooxml, embedded labels, table is Meterreading);

Meterreading:
Load *
   if(Text(Meternumber) = Peek(Text(Meternumber)), Meterreading - Peek(Meterreading),0 ) as Consumption
 Resident temp
Order by Meternumber,Date ASC;
Drop table temp;

[reporting_neu]

You made my day 😀 

I didn't have any sorting, so the condition was never met, so all values ​​were 0.

Thanks to your help it's working now. Thank you very much!!!