Usage since last reading formula

[winnie_the_bish]

Hi,

Problem Context: I'm creating an app in MS PowerApps to record and view meter readings (eg. Electricity, Water). I'm using an Excel Online workbook as the data source.


Issue: Users will input readings but it is highly beneficial to calculate the usage since the last reading and display it at various points within the app. I'm not sure how to go about calculating this in the Excel workbook.

I've attached an example workbook and highlighted the usage column in the readings table. I've also included an image from an app that is similar to what I'm trying to do.


Please note that there are multiple meters within the readings table and the dates of readings are not necessarily consistent.

Let me know if you need more info and thanks in advance!

[Bernie Deitrick]

Take a look at the Excel function FORECAST, used like

=FORECAST(TODAY(),UsageValues,DateValues)

Like so:

=FORECAST(TODAY(),B:B,A:A)

That will use a linear extrapolation based for today's date based on historical usage.

[winnie_the_bish]

Hi Bernie, thanks for your help.

Unfortunately I didn't word my question well. I want to calculate the actual usage between each reading.

So if I have a reading of 23 for 'Meter A' on the 2/3/18 and then have a reading of 32 for 'Meter A' on the 4/3/18 (2 days later) I should get a usage of 9 attributed to the 4/3/18 record. In other words I used 9 units between each reading.

Meter	Reading	Date	Usage
A	0	1/3/18	0
A	23	2/3/18	23
B	0	3/3/18	0
A	32	4/3/18	9
B	15	4/3/18	15
B	76	7/3/18	61
A	70	8/3/18	38

[Bernie Deitrick]

In D2, array-enter (enter using Ctrl-Shift-Enter) the formula (Note: I don't know if the app you are using supports array formulas - only one way to find out )

=IF(COUNTIF($A$1:A2,A2)=1,B2,B2-MAX(IF($A$1:A1=A2,$B$1:B1)))

and copy down to match your values in your table.

This assumes that the readings are always greater as time goes on - if they can reset, then we will need another check.

[winnie_the_bish]

Thank you Bernie!

This formula works brilliantly.


I've just now realised that MS PowerApps doesn't read fields with formulas

It looks like I'll have to find another way to do this, I might be able to replicate this formula in PowerApps.

I'll mark as solved though, thanks again for your help