cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
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
CP101/01/2000:01:00145null
CP101/01/2000:02:00155null
CP101/01/2000:03:00165null
CP101/01/2000:00:00null100
CP101/01/2000:01:00null110
CP101/01/2000:02:00null115
CP101/01/2000:03:00null130

 

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:00510
   00:02:00105
   00:03:001015

 

 

Any help would be appreciated.

 

Thanks!

2 REPLIES 2
rkandathil
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!

Anonymous
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

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