Hourly difference between cumulative total

[ausphil]

Hello,

I have a table that has person, time of reading, and total steps reading from their pedometer. The data syncs every hour. I would like to create a calculated column (Hourly Steps) that returns the difference between the total steps reading for each hour. Appreciate any assistance, thanks!

Person	SyncTime	Total Steps		Hourly Steps
John	12-09-18 7:00	20000
Martin	12-09-18 9:00	599
James	12-09-18 6:00	125
James	12-09-18 7:00	472		347
John	12-09-18 8:00	20120		120
Martin	12-09-18 10:00	752		153

[balaganeshv2201 · Accepted solution]

Hourly Steps = if(ISBLANK(CALCULATE(SUM([Total Steps]),FILTER(Table1,Table1[Person]=EARLIER(Table1[Person])&& Table1[SyncTime]<EARLIER(Table1[SyncTime])))) ,BLANK(), [Total Steps]- CALCULATE(max([Total Steps]),FILTER(Table1,Table1[Person]=EARLIER(Table1[Person])&& Table1[SyncTime]<EARLIER(Table1[SyncTime]))))

------------------

try this

[ausphil]

---

@balaganeshv2201 wrote:

Hourly Steps = if(ISBLANK(CALCULATE(SUM([Total Steps]),FILTER(Table1,Table1[Person]=EARLIER(Table1[Person])&& Table1[SyncTime]<EARLIER(Table1[SyncTime])))) ,BLANK(), [Total Steps]- CALCULATE(max([Total Steps]),FILTER(Table1,Table1[Person]=EARLIER(Table1[Person])&& Table1[SyncTime]<EARLIER(Table1[SyncTime]))))

------------------

try this

---

Thank you sir, this worked!

[Ashish_Mathur]

Hi,

Try this calculated column formula

=if(ISBLANK(CALCULATE(SUM([Total Steps]),FILTER(Table1,Table1[Person]=EARLIER(Table1[Person])&&Table1[SyncTime]<EARLIER(Table1[SyncTime])))),BLANK(),[Total Steps]-CALCULATE(SUM([Total Steps]),FILTER(Table1,Table1[Person]=EARLIER(Table1[Person])&&Table1[SyncTime]<EARLIER(Table1[SyncTime]))))

Hope this helps.

[ausphil]

Hi Ashish,

Thanks for providing that formula. It works for when there are only 2 synctimes but once you get to 3 synctimes, it will subtract all the previous synctime total steps too.

[Ashish_Mathur]

Hi,

Try this

=if(ISBLANK(CALCULATE(SUM([Total Steps]),FILTER(Table1,Table1[Person]=EARLIER(Table1[Person])&&Table1[SyncTime]<EARLIER(Table1[SyncTime])))),BLANK(),[Total Steps]-LOOKUPVALUE([Total Steps],[Person],[Person],[SyncTime],CALCULATE(MAX([Synctime]),FILTER(Table1,Table1[Person]=EARLIER(Table1[Person])&&Table1[SyncTime]<EARLIER(Table1[SyncTime]))))

[ausphil]

---

@Ashish_Mathur wrote:

Hi,

Try this

=if(ISBLANK(CALCULATE(SUM([Total Steps]),FILTER(Table1,Table1[Person]=EARLIER(Table1[Person])&&Table1[SyncTime]<EARLIER(Table1[SyncTime])))),BLANK(),[Total Steps]-LOOKUPVALUE([Total Steps],[Person],[Person],[SyncTime],CALCULATE(MAX([Synctime]),FILTER(Table1,Table1[Person]=EARLIER(Table1[Person])&&Table1[SyncTime]<EARLIER(Table1[SyncTime]))))

---

Hi Ashish thanks for the updated formula. I tested the previous respondent's and that worked so I stuck with that.

[Ashish_Mathur]

Hi,

I do not understand.  My formula will deduct the total steps appeaing against the sync time just prior to the sync time of the current row from the total steps of the current row.