Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
fabiofurlanbr
Frequent Visitor

Diferença entre datas baseado em uma coluna Status

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á

 

LojaStatus PickupHorário_Email
Hospital 01Ativo13/06/2022 18:32
Hospital 01Inativo13/06/2022 16:36
Shopping 02Ativo14/06/2022 01:12
Shopping 02Ativo14/06/2022 01:29
Shopping 02Ativo14/06/2022 02:07
Shopping 02Inativo14/06/2022 01:05
Shopping 02Inativo14/06/2022 01:28
Shopping 02Inativo14/06/2022 02:03
Shopping 02Inativo14/06/2022 02:21
Hospital 03Ativo13/06/2022 21:42
Hospital 03Inativo13/06/2022 20:02
Hotel 02Ativo14/06/2022 05:28
Hotel 02Inativo14/06/2022 05:27
Hotel 03Ativo13/06/2022 12:11
Hotel 03Inativo13/06/2022 12:05
2 ACCEPTED SOLUTIONS
rohit_singh
Solution Sage
Solution Sage

Hello @fabiofurlanbr ,

Please try creating a measure like this :

rohit_singh_0-1655255634531.png

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! 🙂

View solution in original post

Hello @fabiofurlanbr ,

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

rohit_singh_0-1655299688572.png

 

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

Total Downtime =

SUMX(
ALLEXCEPT(DowntimeDowntime[Loja]),
Downtime[DateDiff (min)]
)

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

Kind regards,

Rohit

View solution in original post

4 REPLIES 4
rohit_singh
Solution Sage
Solution Sage

Hello @fabiofurlanbr ,

Please try creating a measure like this :

rohit_singh_0-1655255634531.png

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! 🙂

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.

Hello @fabiofurlanbr ,

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

rohit_singh_0-1655299688572.png

 

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

Total Downtime =

SUMX(
ALLEXCEPT(DowntimeDowntime[Loja]),
Downtime[DateDiff (min)]
)

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

Kind regards,

Rohit

Hello @rohit_singh 

Fantastic

Thank you so much again.

 

Kind Regards

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors