Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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    DateTimeDay ValueNight Value
CP101/01/20      00:00:00    140null


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  



Any help would be appreciated.



Frequent Visitor

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!

Not applicable

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).

Helpful resources

PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.