cancel
Showing results for
Did you mean:
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

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

 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!

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

Announcements

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.

Microsoft named a Leader in The Forrester Wave

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

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.

Top Solution Authors
Top Kudoed Authors