Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!
Solved! Go to 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() )
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
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() )
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |