Sum values between datetimes in related table

[ausphil]

Hello, I have ShiftTable like so:

ShiftTimeStart	ShiftTimeEnd
12-09-18 7:00	12-09-18 19:00
12-09-18 19:00	13-09-18 7:00
13-09-18 7:00	13-09-18 19:00
13-09-18 19:00	14-09-18 7:00
14-09-18 7:00	14-09-18 19:00
14-09-18 19:00	15-09-18 7:00

And my data is like so:

Person	SyncTime	Total Steps	Hourly Steps
John	12-09-18 7:00	20000
John	12-09-18 8:00	20754	754
John	12-09-18 9:00	21076	322
John	12-09-18 10:00	21532	456
John	12-09-18 11:00	21544	12
John	12-09-18 12:00	21558	14
John	12-09-18 13:00	21577	19
John	12-09-18 14:00	21831	254
John	12-09-18 15:00	22164	333
John	12-09-18 16:00	22664	500
John	12-09-18 17:00	22697	33
John	12-09-18 18:00	22737	40
John	12-09-18 19:00	22787	50
Martin	12-09-18 7:00	11568
Martin	12-09-18 8:00	11808	240
Martin	12-09-18 9:00	11952	144
Martin	12-09-18 10:00	12237	285
Martin	12-09-18 11:00	12303	66
Martin	12-09-18 12:00	12377	74
Martin	12-09-18 13:00	12476	99
Martin	12-09-18 14:00	12801	325
Martin	12-09-18 15:00	12912	111
Martin	12-09-18 16:00	13362	450
Martin	12-09-18 17:00	13439	77
Martin	12-09-18 18:00	14239	800
Martin	12-09-18 19:00	14679	440

I would like to add a calculated column that sums all the hourly steps for each person between the ShiftTable times, with the value only being in the row corresponding to that Shift time like so:

Person	SyncTime	Total Steps	Hourly Steps	Shift Steps
John	12-09-18 7:00	20000
John	12-09-18 8:00	20754	754
John	12-09-18 9:00	21076	322
John	12-09-18 10:00	21532	456
John	12-09-18 11:00	21544	12
John	12-09-18 12:00	21558	14
John	12-09-18 13:00	21577	19
John	12-09-18 14:00	21831	254
John	12-09-18 15:00	22164	333
John	12-09-18 16:00	22664	500
John	12-09-18 17:00	22697	33
John	12-09-18 18:00	22737	40
John	12-09-18 19:00	22787	50	2787
Martin	12-09-18 7:00	11568
Martin	12-09-18 8:00	11808	240
Martin	12-09-18 9:00	11952	144
Martin	12-09-18 10:00	12237	285
Martin	12-09-18 11:00	12303	66
Martin	12-09-18 12:00	12377	74
Martin	12-09-18 13:00	12476	99
Martin	12-09-18 14:00	12801	325
Martin	12-09-18 15:00	12912	111
Martin	12-09-18 16:00	13362	450
Martin	12-09-18 17:00	13439	77
Martin	12-09-18 18:00	14239	800
Martin	12-09-18 19:00	14679	440	3111

SyncTime is related to ShiftTimeStart.

Appreciate your help!!

[v-cherch-msft]

Hi @ausphil

You may try to add an index column in Query Editor for your Data table. Then create the calculated column with EARLIER Function as below:

Column =
IF (
    RELATED ( ShiftTime[ShiftTimeEnd] ) = Data[SyncTime],
    CALCULATE (
        SUM ( Data[Hourly Steps] ),
        FILTER (
            ALL ( Data ),
            Data[Person] = EARLIER ( Data[Person] )
                && Data[Index] <= EARLIER ( Data[Index] )
        )
    ),
    BLANK ()
)

Regards,

Cherie

[ausphil · Accepted solution]

OK I think I got it, changed the relationship to ShiftTime[ShiftTimeEnd] and used:

Column = IF (
    RELATED ( ShiftTime[ShiftTimeEnd] ) = Data[SyncTime], 
    CALCULATE (
        SUM ( Data[Hourly Steps] ),
        FILTER (
            ALL ( Data ),
            Data[Person]  = EARLIER ( Data[Person]  )
            && Data[SyncTime] > EARLIER(RELATED(ShiftTime[ShiftTimeStart]))
            && Data[SyncTime] <= EARLIER(RELATED(ShiftTime[ShiftTimeEnd]))
        )
    ),
    BLANK()
)

[v-cherch-msft]

Hi @ausphil

Glad to hear that you've solved it. please accept your reply as solution, that way, other community members will easily find the solution when they get same issue.

Regards,

Cherie

[ausphil]

Hi Cherie,

Thank you for your suggestion. That works for the first shift, but it will cumulatively total all others after that. I want the number to only total between the shifts. So for example, using your formula, when I have data for the 13th Sep, then the result at 13-09-18 19:00 includes the Shift Total for 12-09-18 19:00. 

Thanks

Phil