Subtracting current hour's value from previous hour's value

[Anonymous]

Is there a DATEADD equivalent for hours?

The smallest interval for DATEADD is DAY, but I need it to be HOUR.

I have a running total number that grows once per hour, I need to make a measure that calculates the hourly consumption.

For example for monthly calculations I use

Monthly = SUM('Readings'[VALUE]) - CALCULATE(SUM('Readings'[VALUE]), DATEADD('Date'[Date], -1, MONTH))

I need a similar solution to calculate hourly consumption for each of the 24 hours.

Sample table 'Readings':

Consumption Point	Date	Time	Day Value	Night Value
CP1	01/01/20	00:00:00	140	null
CP1	01/01/20	00:01:00	145	null
CP1	01/01/20	00:02:00	155	null
CP1	01/01/20	00:03:00	165	null
CP1	01/01/20	00:00:00	null	100
CP1	01/01/20	00:01:00	null	110
CP1	01/01/20	00:02:00	null	115
CP1	01/01/20	00:03:00	null	130

So I would need a measure that knows for example on 01/01/20 at hour 1 (01:00) the day consumption was 5 (145-140) etc. And for 00:00:00 the measure would have to subtract from 00:00 the 23:00 of the previous date and so forth.

When googling this problem I saw a solution with indexing the rows but, since there are more than one (total 4) consumption running totals for every hour then indexing the rows wouldn't work.

I would like to use these measures (one for each type of consumption) in a matrix table with a slicer next to it where the user can choose the date(s). The matrix table would look something like this:

Consumption Point/Hour	Day Consumption	Night Consumption
CP1
00:01:00	5	10
00:02:00	10	5
00:03:00	10	15

Any help would be appreciated.

Thanks!

[Anonymous]

Hey @Anonymous ,

You could try: [Consumption Point/ Hour] +/- TIME( Hrs, Mins, Secs )
eg. [00:14:00] - TIME(2,30,30) = 11:29:30

Hope that helps!

[Anonymous]

Could you explain this a bit more, I don't really see at the moment how this would work. The Consumption Point/Hour are two different columns in the database I would just use them as rows in the matrix column. And I need to do the calculation of (for example for Day Value) current hour day value minus last hour's day value (since the day value numbers in the database are running totals).