Cumulative Sum, reset by recurring group

[Etienne123]

I need to calculate the time an animal spends in a sector, and only for the time it resides in that sector - an animal can move in and out of a sector many times. Each "visit" constitutes a new visit and the reset must apply. In the data below, you can see how animal 100A visited sector A, then moved to sector B, then moved back to sector A.

RodentId : Cage : Sector : Time : Seconds : Index

100A: XX: A: 15:20:21: 1 second: 1
100A: XX: A: 15:20:22: 1 second: 2
100A: XX: B: 15:20:23: 1 second: 3
100A: XX: B: 15:20:24: 1 second: 4
100A: XX: B: 15:20:25: 1 second: 5
100A: XX: A: 15:20:26: 1 second: 6
100A: XX: A: 15:20:27: 1 second: 7

Below is my current calculation, and the fact that sector is recurring is causing the "revisit" to sector A, not to reset the calculation and continues from the previous visit. How do I overcome this?

GroupPeriod =
SUMX (
    FILTER (
        'Sample data 1',
        EARLIER ( 'Sample data 1'[rodentId]) = 'Sample data 1'[rodentId]
     && EARLIER ( 'Sample data 1'[cage]) = 'Sample data 1'[cage]
     && EARLIER ( 'Sample data 1'[Sector] ) = 'Sample data 1'[Sector] &&
     && EARLIER ( 'Sample data 1'[Index] ) >= 'Sample data 1'[Index]
    ),
    'Sample data 1'[Period]
)

[v-yuezhe-msft]

@Etienne123,

Create the following columns in your table.

Group = MINX(FILTER(ALL('Sample data 1'),'Sample data 1'[Sector]<>EARLIER('Sample data 1'[Sector])&&'Sample data 1'[Time]>EARLIER('Sample data 1'[Time])),'Sample data 1'[Time])

GroupPeriod = 
SUMX (
    FILTER (
        'Sample data 1',
        EARLIER ( 'Sample data 1'[rodentId]) = 'Sample data 1'[rodentId]
     && EARLIER ( 'Sample data 1'[cage]) = 'Sample data 1'[cage]
     && EARLIER ( 'Sample data 1'[Sector] ) = 'Sample data 1'[Sector] 
&& EARLIER('Sample data 1'[Group])='Sample data 1'[Group]
     && EARLIER ( 'Sample data 1'[Index] ) >= 'Sample data 1'[Index]
    ),
    'Sample data 1'[Seconds]
)

Regards,
Lydia

[Etienne123]

Hi

I cannot get your suggestion to work. Note that in my dataset period constiture what you termed as "seconds" as it shows each 0.5 interval for each record.

Group =
MINX(
FILTER(
ALL('Sample data 1'),
'Sample data 1'[Sector]<>EARLIER('Sample data 1'[Sector])
&&'Sample data 1'[Time]>EARLIER('Sample data 1'[Time]))
,'Sample data 1'[Time])

And then

GroupPeriod =
SUMX (
FILTER (
'Sample data 1',
EARLIER ( 'Sample data 1'[rodentId]) = 'Sample data 1'[rodentId]
&& EARLIER ( 'Sample data 1'[cage]) = 'Sample data 1'[cage]
&& EARLIER ( 'Sample data 1'[Sector] ) = 'Sample data 1'[Sector]
&& EARLIER('Sample data 1'[Group]) = 'Sample data 1'[Group]
&& EARLIER ( 'Sample data 1'[Index] ) >= 'Sample data 1'[Index]
), 'Sample data 1'[Period]
)

The Group formula do not group the cohort of visits to a sector together. 

[v-yuezhe-msft]

@Etienne123,

Please share the complete table that contains period column and post expected result here.

Regards,
Lydia

[Etienne123]

Let me try and explain. This is a visual solutio tied to IoT sensor measures in a cage measurting the movement of animals. Imagine a cage, with 3 animals, moving freely between 6 sectors in a cage. Each sector has an IoT device that takes a measure every 0.5 seconds. The business outcome is to measure movement and how long an animal spends in any one sector. The data literally looks like the images below (I am trying to calculate GroupPeriod - it cumulatively sum each 0.5 second measure until there is a change in Cage, Rodent (Animal), and Sector when it should revert back to 0.5 and start cumulatively summing again).

I have some formulae that works somewhat, but there is still something wrong as it does not work 100% as it does not revert back to 0.5 in all instances:

Image 1 - you can see that the same rodent, changed from sector 3 to 2 and the GroupPeriod correctly reverted back to 0.5

Image 2 - you can see that the same rodent, changed back from 2 to 3, but GroupPeriod continue the cumulatifve sum from the previous change (it hould have reverted back to 0.5, but continues with 21.5 seconds

Image 3 - you can see here how there is a change in rodent and so the GroupPeriod correctly reverted back to 0.5 seconds

Current formulae below the images:

Image 1Image 2Image 3

Formulae:

GroupVar - creates a startying row for each series (I do not think this works correctly, but I cant figure out why)

GroupVar = VAR S1 = 'Sample data 1'[Sector] VAR S2 = CALCULATE(MIN('Sample data 1'[Sector]),FILTER('Sample data 1','Sample data 1'[Index] = EARLIER('Sample data 1'[Index])-1)) VAR S3 = if(S1<>S2,'Sample data 1'[Time],BLANK()) RETURN S3

GroupMinTime - fills the above valyue down within the series

GroupMinTime = CALCULATE(MAX('Sample data 1'[GroupVar]),FILTER(all('Sample data 1'),'Sample data 1'[Index] <= EARLIER('Sample data 1'[Index])))

Group - Creates a proper unique group to include Rodebnt, Sector, Cage and the above

Group = 'Sample data 1'[GroupMinTime] & 'Sample data 1'[rodentId] & 'Sample data 1'[Sector] & 'Sample data 1'[cage]

GroupPeriod - creates the cumulative sum

GroupPeriod =
SUMX (
FILTER (
'Sample data 1',
EARLIER ( 'Sample data 1'[rodentId]) = 'Sample data 1'[rodentId]
&& EARLIER ( 'Sample data 1'[cage]) = 'Sample data 1'[cage]
&& EARLIER ( 'Sample data 1'[Sector] ) = 'Sample data 1'[Sector]
&& EARLIER('Sample data 1'[Group]) = 'Sample data 1'[Group]
&& EARLIER ( 'Sample data 1'[Index] ) >= 'Sample data 1'[Index]
), 'Sample data 1'[Period]
)

Any ideas greatly appreciated

[Etienne123 · Accepted solution]

Issue resolved for anyone interested. The issue was with the formula GroupMinTime. It required a statement to enforce a reset on RodentId.

GroupMinTime =
CALCULATE(
MAX(
'Sample data 1'[GroupVar])
,FILTER(all('Sample data 1')
,'Sample data 1'[Index] <= EARLIER('Sample data 1'[Index])
&& 'Sample data 1'[RodentId] = EARLIER('Sample data 1'[RodentId])
))