cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
glynison
Frequent Visitor

Count Consecutive Months with reset | Contar meses consecutivos com reset

<EN>

Hi!
I would like help to count how long a certain problem has been occurring in a certain equipment, restarting the count when it does not occur in a month.
Each device can have 2 directions (Ascending and Descending), and there is always 1

 

and;

 

Each equipment can havemany problems.

The goal is to bring the matrix below into Power BI using the same layout I have in another analysis.

The example has a circle that will be replaced by the number of consecutive months.

I have attached the two images for you to see.

 

Example:

Cod.EquipamentoData da VistoriaSentidoSubcategoriaFotoRESULTADO
GOR2002002124/01/2020DecrescenteDispositivo luminoso/Display inoperante ou defeituosohttps://storage.googleapis.com/coletum-media/8248221d-b084-4364-b16a-6391cfa4eaaa/605be6d33287e.jpg1
GOR2002002124/03/2020DecrescenteDispositivo luminoso/Display inoperante ou defeituosohttps://storage.googleapis.com/coletum-media/f2e7f81f-5b6a-4b72-89c2-deae693a7db3/605be6b8959ec.jpg1
GOR2002002122/04/2020DecrescenteDispositivo luminoso/Display inoperante ou defeituosohttps://storage.googleapis.com/coletum-media/ac85f7b0-b932-44b2-9282-94678fb977d5/6081ac388fd91.jpg2
GOR2002002119/05/2020DecrescenteDispositivo luminoso/Display inoperante ou defeituosohttps://storage.googleapis.com/coletum-media/defeb194-1853-4655-9606-67148bd617eb/20210519145915980....3
GOR2002002112/02/2021DecrescenteDispositivo luminoso/Display inoperante ou defeituosohttps://storage.googleapis.com/coletum-media/aed640b2-233b-4b8a-a7db-b158d154bae2/20210611135036614....1
GOR2002002112/03/2021DecrescenteDispositivo luminoso/Display inoperante ou defeituosohttps://storage.googleapis.com/coletum-media/7970ce29-c501-43c6-af0d-e40cab7c603d/20210611134927784....2
GOR2002002109/04/2021DecrescenteDispositivo luminoso/Display inoperante ou defeituosohttps://storage.googleapis.com/coletum-media/c105517f-c8a4-44b1-8053-abed09acdf45/20210709085247811....3
GOR2002002109/07/2021DecrescenteDispositivo luminoso/Display inoperante ou defeituosohttps://storage.googleapis.com/coletum-media/a4b46132-80ae-4224-a12e-75e3b5fcd114/20210709085309077....1
GOR2002002125/08/2021DecrescenteDispositivo luminoso/Display inoperante ou defeituosohttps://storage.googleapis.com/coletum-media/fa53e104-36b1-4ff7-a80f-730a632b3202/20210825095452096....2
GOR2002002110/09/2021DecrescenteDispositivo luminoso/Display inoperante ou defeituosohttps://storage.googleapis.com/coletum-media/a3e832b6-d10a-4031-a5ef-304dfdbe20ab/20210825095428001....1

 

The base sheet is here

 

 

<BR>

Olá!
Gostaria de ajuda para contar há quanto tempo um determinado problema está ocorrendo em deternimado equipamento, reiniciando a contagem quando não ocorrer em algum mês.
Cada equipamento pode ter 2 sentidos (Crescente e Decrescente), sendo que sempre tem 1

 

e;

 

Cada equipamento pode ter vários problemas.

 

A planilha base está aqui.

 

O objetivo é levar a matriz abaixo para dentro do Power BI usando o mesmo layout que tenho em outra análise.

O exemplo tem um círculo que será substituído pelo número de meses consecutivos.

Anexei as duas imagens para vocês verem.

 

Exemplo:

Cod.EquipamentoData da VistoriaSentidoSubcategoriaFotoRESULTADO
GOR2002002124/01/2020DecrescenteDispositivo luminoso/Display inoperante ou defeituosohttps://storage.googleapis.com/coletum-media/8248221d-b084-4364-b16a-6391cfa4eaaa/605be6d33287e.jpg1
GOR2002002124/03/2020DecrescenteDispositivo luminoso/Display inoperante ou defeituosohttps://storage.googleapis.com/coletum-media/f2e7f81f-5b6a-4b72-89c2-deae693a7db3/605be6b8959ec.jpg1
GOR2002002122/04/2020DecrescenteDispositivo luminoso/Display inoperante ou defeituosohttps://storage.googleapis.com/coletum-media/ac85f7b0-b932-44b2-9282-94678fb977d5/6081ac388fd91.jpg2
GOR2002002119/05/2020DecrescenteDispositivo luminoso/Display inoperante ou defeituosohttps://storage.googleapis.com/coletum-media/defeb194-1853-4655-9606-67148bd617eb/20210519145915980....3
GOR2002002112/02/2021DecrescenteDispositivo luminoso/Display inoperante ou defeituosohttps://storage.googleapis.com/coletum-media/aed640b2-233b-4b8a-a7db-b158d154bae2/20210611135036614....1
GOR2002002112/03/2021DecrescenteDispositivo luminoso/Display inoperante ou defeituosohttps://storage.googleapis.com/coletum-media/7970ce29-c501-43c6-af0d-e40cab7c603d/20210611134927784....2
GOR2002002109/04/2021DecrescenteDispositivo luminoso/Display inoperante ou defeituosohttps://storage.googleapis.com/coletum-media/c105517f-c8a4-44b1-8053-abed09acdf45/20210709085247811....3
GOR2002002109/07/2021DecrescenteDispositivo luminoso/Display inoperante ou defeituosohttps://storage.googleapis.com/coletum-media/a4b46132-80ae-4224-a12e-75e3b5fcd114/20210709085309077....1
GOR2002002125/08/2021DecrescenteDispositivo luminoso/Display inoperante ou defeituosohttps://storage.googleapis.com/coletum-media/fa53e104-36b1-4ff7-a80f-730a632b3202/20210825095452096....2
GOR2002002110/09/2021DecrescenteDispositivo luminoso/Display inoperante ou defeituosohttps://storage.googleapis.com/coletum-media/a3e832b6-d10a-4031-a5ef-304dfdbe20ab/20210825095428001....1

 

 

helpBI.pnghelpBI1.png

1 ACCEPTED SOLUTION

@glynison Here is a more complex example of an implementation of Cthulhu where there also was not an Index but just a date. Thus, the Index is created dynamically. The Index is crucial as this is how "skips" are identified, when the difference between the Index is two consequetive rows is >1:

Solved: Re: Consecutive Successes in a Time Frame - Microsoft Power BI Community

 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

@glynison Seems like a variation on Cthulhu: Cthulhu - Microsoft Power BI Community


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

@Greg_Deckler obrigado pelo conteúdo. Estou com dificuldades para adaptar aos meus dados.
Seu exemplo não tem uma coluna de datas e apenas a coluna 'Animal' está como ID.

 

No meu caso, tenho 3 colunas para filtrar as análises que servem como uma espécie de ID Composto (Cod.Equipamento, Sentido, Subcategoria) além da coluna 'Data da Vistoria'.

 

Estou fazendo as alterações e testando se os resultados estão como esperado.
Ainda não consegui, mas obrigado pela orientação. Foi a mais clara que encontrei até agora.

@glynison Here is a more complex example of an implementation of Cthulhu where there also was not an Index but just a date. Thus, the Index is created dynamically. The Index is crucial as this is how "skips" are identified, when the difference between the Index is two consequetive rows is >1:

Solved: Re: Consecutive Successes in a Time Frame - Microsoft Power BI Community

 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.