Count same value in interval

[guilhermemedin1]

Hello,

I have a problem to identify how many days a machine operates in a role every time it starts operating. If it was already operating on day one it should not be counted. The output for days in a role would be as follows.

Apreciate any help in this matter.

Day	Machine	Status	Status Change	Days in a role
day 1	Machine 1	Operating		0
day 1	Machine 2	Operating		0
day 1	Machine 3	Operating		0
day 2	Machine 1	Stopped		0
day 2	Machine 2	Operating	Started operating	1
day 2	Machine 3	Operating		1
day 3	Machine 1	Stopped		0
day 3	Machine 2	Operating	Started operating	1
day 3	Machine 3	Stopped	Stopped	0

[guilhermemedin1 · Accepted solution]

Thank you for following the question. The ideia is to count the number of days the machines are operating after starting. Machine 2 started operating on day 2 and is still on, hence it counts day 2 and 3.

I was able to come up with this solution.

Days_in_a_role =

VAR dateOperationChange = CALCULATE(MINX('Table','Table'[day]),

FILTER('Table',

'Table'[Status Change] = EARLIER('Table'[Status Change]) &&

'Table'[Status Change] = "Started Operating"

)) return

VAR countChangeState = CALCULATE(

COUNTX('Table','Table'[Status Change]),

FILTER('Table',

'Table'[Machine] = EARLIER('Table'[Machine]) &&

'Table'[Status Change] = "Started Operating")

) return

VAR firstRowIsOperation = CALCULATE(MINX('Table', 'Table'[Status]), FILTER('Table', 'Table'[Machine] = EARLIER('Table'[Machine]))) = "Started Operating" return

VAR checkIsOpational =

IF( firstRowIsOperation && countChangeState = 0, false,

IF(firstRowIsOperation && countChangeState > 0, true,

IF(firstRowIsOperation = false && countChangeState > 0, true, false)

)

) return

VAR checkIsOperation = IF(CALCULATE(

MAXX('Table','Table'[Status]),

FILTER('Table',

'Table'[Machine] = EARLIER('Table'[Machine]) &&

'Table'[Status] = EARLIER('Table'[Status]) &&

checkIsOpational = true &&

'Table'[day] >= dateOperationChange

)) = "Started Operating", 1, 0) return checkIsOperation

[lbendlin]

Machine 2 never stopped - why is the result 1 ?  Please explain the logic in more detail and maybe provide some more sample data.