Diferença entre datas baseado em uma coluna Status

[fabiofurlanbr]

Boa Tarde.

Preciso muito de uma ajuda, preciso calcular o tempo de inatividade de uma determinada loja (DateDiff ) ,  porém eu não tenho os campos de datas separados , exemplo , data_inativo e data_ativo ,  já tentei de várias formas , mas infelizmente não consegue, segue exemplo do dataset que é um csv.

Eu preciso saber o tempo de inatividade quando os status são acionados  ,exemplo Inativo as 10hs e Ativo as 12 ,  preciso deste calculo de inatividade de 2 horas.

Já tentei de algumas formas com Dax e PowerQuery , mas os resultados não foram bem sucedidos

Agradeço muito desde já

Loja	Status Pickup	Horário_Email
Hospital 01	Ativo	13/06/2022 18:32
Hospital 01	Inativo	13/06/2022 16:36
Shopping 02	Ativo	14/06/2022 01:12
Shopping 02	Ativo	14/06/2022 01:29
Shopping 02	Ativo	14/06/2022 02:07
Shopping 02	Inativo	14/06/2022 01:05
Shopping 02	Inativo	14/06/2022 01:28
Shopping 02	Inativo	14/06/2022 02:03
Shopping 02	Inativo	14/06/2022 02:21
Hospital 03	Ativo	13/06/2022 21:42
Hospital 03	Inativo	13/06/2022 20:02
Hotel 02	Ativo	14/06/2022 05:28
Hotel 02	Inativo	14/06/2022 05:27
Hotel 03	Ativo	13/06/2022 12:11
Hotel 03	Inativo	13/06/2022 12:05

[rohit_singh · Accepted solution]

Hello @fabiofurlanbr ,

Please try creating a measure like this :

DateDiff (min) =

var _currtime = min(Downtime[Horário_Email])

var _prevtime =

CALCULATE(

MAX(Downtime[Horário_Email]),

FILTER(ALLEXCEPT(Downtime, Downtime[Loja]), Downtime[Horário_Email] < _currtime

)

)

var _datediff = DATEDIFF(_prevtime, _currtime, MINUTE)

RETURN

if( max(Downtime[Status Pickup]) = "Ativo", _datediff, 0 )

This will give you downtime in minutes.

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

[rohit_singh · Accepted solution]

Hello @fabiofurlanbr ,

Please try creating a measure like this :

DateDiff (min) =

var _currtime = min(Downtime[Horário_Email])

var _prevtime =

CALCULATE(

MAX(Downtime[Horário_Email]),

FILTER(ALLEXCEPT(Downtime, Downtime[Loja]), Downtime[Horário_Email] < _currtime

)

)

var _datediff = DATEDIFF(_prevtime, _currtime, MINUTE)

RETURN

if( max(Downtime[Status Pickup]) = "Ativo", _datediff, 0 )

This will give you downtime in minutes.

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

[fabiofurlanbr]

Hello @rohit_singh

This solution is great.

First of all, thank you very much, it worked perfectly.

There is a way to present the sum, total inactivity for each store, it can be with another measure as well.

[rohit_singh · Accepted solution]

Hello @fabiofurlanbr ,

You're welcome. Happy to help. Of course you can try something like this :

Since you're going to summarize a measure, you will use SUMX in this case to get the result.

Total Downtime =

SUMX(

ALLEXCEPT(Downtime, Downtime[Loja]),

Downtime[DateDiff (min)]

)

Please do mark my answer as the solution if it resolves your issue!

Kind regards,

Rohit

[fabiofurlanbr]

Hello @rohit_singh 

Fantastic

Thank you so much again.

Kind Regards