PINTAR 366º DIA EM UM GRÁFICO DE GANTT

[FrancoAndre]

Olá à todos!

Galera fiz um gráfico de gantt em um painel de matriz utilizando umas gambiarras graças ao vídeo do canal How To Power BI: https://www.youtube.com/watch?v=QugkBqc-jIs&t=347s

Um breve resumo do que é feito no vídeo é que ele utiliza a data inicio e fim de um projeto e faz uma medida DAX para preencher com um valor os intervalos entre as datas. O que não estiver no intervalo ele informa um valor zero e o que estiver dentro é 1, depois ele altera a cor da fonte e da tela de fundo considerando os número 0 = branco e 1 = qlq outra cor diferente de branco.

O painel ficou ótimo e super atendeu o que pretendia, porém precisei fazer algumas condições:

• se a diferença da data fim do projeto com hoje for menor que zero = 1 (No Prazo - Verde)
• se a diferença da data fim do projeto com hoje  && a diferença com hoje e a data fim projeto for menor ou igual a 30 = 3 (Vence 30D - Amarelo)
• se a data do início do projeto e o fim for verdadeira = 2 (Vencido - Vermelho)
• se houver data ausente ou em branco no projeto = 4 (Sem Prazo - Cinza)

Resultando da medida ficou assim:

 

E o painel ficando dessa forma:

 

Agora preciso incluir mais uma condição no switch e preciso de uma ajuda no DAX para criar essa lógica. A nova condição é que a cada 1 ano no 366º dia deve aparecer o número 5 (cor azul) considerando a partir do início de cada projeto, basta informar o número 5 que a condição da cor eu crio. A regra deve seguir até a última data fim do projeto ou EndDate contido na Switch.

Criei um demonstrativo no excel para visualizar melhor o q pretendo:

 

Deixarei em anexo um arquivo pbix com a planilha e com a medida já criada. 

Agradeço aos que puderem ajudar.

projetos gantt.xlsx Ajuda Gantt.pbix

[Joao Raulino]

Fala @FrancoAndre!

Claro mano, vamos passo a passo. O código que adicionamos é esse:

Citar

ProjectPeriod && MOD(DATEDIFF(StartDate, MAX(dCalendario[Data]), DAY), 366) <= 31 && EOMONTH(StartDate,0) <> MAX(dCalendario[Data]), 5,

E a matriz Gantt que criamos esta dividida por meses assim:

Isso de cara já significa duas coisas:

• MIN(dCalendario[Data]) vai ser o primeiro dia do mês indicado no titulo da coluna
• MAX(dCalendario[Data]) vai ser o ultimo dia do mês indicado no titulo da coluna

Agora de volta ao código que adicionamos. A primeira condição vem do teu código original:

Citar

VAR ProjectPeriod =

    MIN(dCalendario[Data]) >= StartDate && MIN(dCalendario[Data]) <= EndDate

Aqui a gente diz para retornar TRUE se a minima data (primeiro dia do mês daquela coluna) for maior ou igual do que a data de inicio do contrato e se a minima data (primeiro dia do mês daquela coluna) for menor ou igual do que a data de termino do contrato. Ou seja, no caso do JOSE RAILTON ali em cima, somente as datas entre 15/05/2019 e 01/10/2022 vão retornar TRUE, o que significa que no Gantt os meses de Maio/19 e Outubro/22 vão estar incluídos na condição.

E esta aqui um dos teus pontos: se você quer que as células sejam pintadas ate depois da data de termino então você não pode usar essa condição ProjectPeriod na tua SWITCH. Você precisa modificar para MIN(dCalendario[Data]) >= StartDate e o código passa a ficar assim agora:

Citar

MIN(dCalendario[Data]) >= StartDate && MOD(DATEDIFF(StartDate, MAX(dCalendario[Data]), DAY), 366) <= 31 && EOMONTH(StartDate,0) <> MAX(dCalendario[Data]), 5,

Mas atenção que aqui ele ainda vai incluir o primeiro mês na condição, que no caso do JOSE RAILTON é o mês de Maio/19. Para excluir esse mês da condição, você precisa dizer para ele só começar a partir do mês seguinte usando a EOMONTH(). O código fica assim agora:

Citar

MIN(dCalendario[Data]) > EOMONTH(StartDate,0)+1 && MOD(DATEDIFF(StartDate, MAX(dCalendario[Data]), DAY), 366) <= 31, 5,

Agora você quer que a célula seja pintada a cada 366 dias a partir da data de inicio do contrato. Para isso primeiro precisamos contar quantos dias se passaram entre o dia da célula e a data de inicio. Para isso usamos a função DATEDIFF() e dizemos para ela contar quantos DIAS existem entre a data de inicio do contrato e o ultimo dia do mês indicado pela coluna do grafico Gantt:

Citar

DATEDIFF(StartDate, MAX(dCalendario[Data]), DAY)

No caso do JOSE RAILTON a data de inicio do contrato é 15/05/2019 e 366 dias depois seria 15/05/2020, ou seja, no mês de Maio/20 deve aparecer o primeiro 5 e a célula deve ser pintada de azul:

Mas no teu exemplo você quer começar a contar a partir do mês seguinte, então você precisa dizer para ele começar a contar a partir do primeiro dia do mês seguinte usando a EOMONTH() novamente:

Citar

MIN(dCalendario[Data]) > EOMONTH(StartDate,0)+1 && MOD(DATEDIFF(EOMONTH(StartDate,0)+1, MAX(dCalendario[Data]), DAY), 366) <= 31, 5,

O ultimo passo agora é fazer ele repetir isso a cada 366 dias. Conseguimos esse resultado usando a função MOD() que divide o valor do primeiro argumento (no nosso caso a quantidade de dias entre o primeiro dia do mês seguinte a data inicial e o ultimo dia do mês da coluna) pelo segundo argumento (no nosso caso 366 dias) e retorna o resto da divisão:

Citar

MOD(DATEDIFF(EOMONTH(StartDate,0)+1, MAX(dCalendario[Data]), DAY), 366) <= 31

Ou seja, o dia 30/06/2020 vai retornar 29 (395/366), o dia 31/07/2020 vai retornar 60 (426/366), o dia 31/08/2020 vai retornar 91 (457/366) e assim vai... ate chegar no dia 30/06/2021 que vai retornar 28 de novo (760/366). Como a gente só quer pintar o mês onde o dia 366 caiu, dizemos para a formula retornar apenas os resultados onde o MOD() é menor ou igual a 31.

O resultado final é esse:

Citar

MIN(dCalendario[Data]) > EOMONTH(StartDate,0)+1 && MOD(DATEDIFF(EOMONTH(StartDate,0)+1, MAX(dCalendario[Data]), DAY), 366) <= 31, 5,

O importante aqui é entender a logica para poder adaptar ela conforme a nossa necessidade, e para sermos capazes de fazer manipulações parecidas em outros projetos. Espero que assim tenha ficado mais claro 😉

Abs!

Editado Março 20 por Joao Raulino

[Joao Raulino]

Suave @FrancoAndre?

Legal esse exemplo ai 👏 Adiciona esse código abaixo como a primeira condição do teu SWITCH, logo depois do TRUE():

Citar

ProjectPeriod && MOD(DATEDIFF(StartDate, MAX(dCalendario[Data]), DAY), 366) <= 31 && EOMONTH(StartDate,0) <> MAX(dCalendario[Data]), 5,

Se essa resposta te ajudou, não se esqueça de marcar como melhor solução 😉

Abs!

 

Editado Março 16 por Joao Raulino

[FrancoAndre]

Em 16/03/2024 em 12:57, Joao Raulino disse:

Suave @FrancoAndre?

Legal esse exemplo ai 👏 Adiciona esse código abaixo como a primeira condição do teu SWITCH, logo depois do TRUE():

Se essa resposta te ajudou, não se esqueça de marcar como melhor solução 😉

Abs!

 

Opa @Joao Raulino, agradeço pelo comentário! 😅

Testei o seu código e vi que em alguns ele está informando o 5 no mês anterior:

[FrancoAndre]

10 minutos atrás, FrancoAndre disse:

Opa @Joao Raulino, agradeço pelo comentário! 😅

Testei o seu código e vi que em alguns ele está informando o 5 no mês anterior:

Outro ponto também é que pretendia prolongar a regra para até depois da data de termino:

[Joao Raulino]

Boa @FrancoAndre!

Também percebi isso enquanto estava fazendo o código. Na realidade a logica da formula esta correta: nesse caso que você apontou o projeto começa no dia 15/Maio/2019 logo 366 dias depois seria 15/Maio/2020:

Você precisa definir qual regra levar em conta (e.g.: só contar a partir do 1o dia do mês seguinte, contar a partir da data de inicio...) e depois ajustar os parâmetros da formula usando EOMONTH() onde for apropriado.

Tenta fazer essa modificação do teu lado usando a formula que compartilhei acima como referencia que vai ser um bom exercício 😉

Abs!

[Joao Raulino]

6 minutos atrás, FrancoAndre disse:

Outro ponto também é que pretendia prolongar a regra para até depois da data de termino

E se quiser prolongar a regra ate o fim do calendário, substitui a tua condição ProjectPeriod && ... por MIN(dCalendario[Data]) >= StartDate && ...

Abs!

[FrancoAndre]

4 horas atrás, Joao Raulino disse:

Boa @FrancoAndre!

Também percebi isso enquanto estava fazendo o código. Na realidade a logica da formula esta correta: nesse caso que você apontou o projeto começa no dia 15/Maio/2019 logo 366 dias depois seria 15/Maio/2020:

Você precisa definir qual regra levar em conta (e.g.: só contar a partir do 1o dia do mês seguinte, contar a partir da data de inicio...) e depois ajustar os parâmetros da formula usando EOMONTH() onde for apropriado.

Tenta fazer essa modificação do teu lado usando a formula que compartilhei acima como referencia que vai ser um bom exercício 😉

Abs!

Cara, tentei entender a lógica por trás da tua formula, mas infelizmente não entendi. Até tentei jogar no chatgpt pra entender melhor a lógica pra alterar e msm assim não consegui.

Se pudesse explicar qual foi a lógica que utilizou na função já ajudaria bastante.

 

[FrancoAndre]

48 minutos atrás, Joao Raulino disse:

Fala @FrancoAndre!

Claro mano, vamos passo a passo. O código que adicionamos é esse:

E a matriz Gantt que criamos esta dividida por meses assim:

Isso de cara já significa duas coisas:

• MIN(dCalendario[Data]) vai ser o primeiro dia do mês indicado no titulo da coluna
• MAX(dCalendario[Data]) vai ser o ultimo dia do mês indicado no titulo da coluna

Agora de volta ao código que adicionamos. A primeira condição vem do teu código original:

Aqui a gente diz para retornar TRUE se a minima data (primeiro dia do mês daquela coluna) for maior ou igual do que a data de inicio do contrato e se a minima data (primeiro dia do mês daquela coluna) for menor ou igual do que a data de termino do contrato. Ou seja, no caso do JOSE RAILTON ali em cima, somente as datas entre 15/05/2019 e 01/10/2022 vão retornar TRUE, o que significa que no Gantt os meses de Maio/19 e Outubro/22 vão estar incluídos na condição.

E esta aqui um dos teus pontos: se você quer que as células sejam pintadas ate depois da data de termino então você não pode usar essa condição ProjectPeriod na tua SWITCH. Você precisa modificar para MIN(dCalendario[Data]) >= StartDate e o código passa a ficar assim agora:

Mas atenção que aqui ele ainda vai incluir o primeiro mês na condição, que no caso do JOSE RAILTON é o mês de Maio/19. Para excluir esse mês da condição, você precisa dizer para ele só começar a partir do mês seguinte usando a EOMONTH(). O código fica assim agora:

Agora você quer que a célula seja pintada a cada 366 dias a partir da data de inicio do contrato. Para isso primeiro precisamos contar quantos dias se passaram entre o dia da célula e a data de inicio. Para isso usamos a função DATEDIFF() e dizemos para ela contar quantos DIAS existem entre a data de inicio do contrato e o ultimo dia do mês indicado pela coluna do grafico Gantt:

No caso do JOSE RAILTON a data de inicio do contrato é 15/05/2019 e 366 dias depois seria 15/05/2020, ou seja, no mês de Maio/20 deve aparecer o primeiro 5 e a célula deve ser pintada de azul:

Mas no teu exemplo você quer começar a contar a partir do mês seguinte, então você precisa dizer para ele começar a contar a partir do primeiro dia do mês seguinte usando a EOMONTH() novamente:

O ultimo passo agora é fazer ele repetir isso a cada 366 dias. Conseguimos esse resultado usando a função MOD() que divide o valor do primeiro argumento (no nosso caso a quantidade de dias entre o primeiro dia do mês seguinte a data inicial e o ultimo dia do mês da coluna) pelo segundo argumento (no nosso caso 366 dias) e retorna o resto da divisão:

Ou seja, o dia 30/06/2020 vai retornar 29 (395/366), o dia 31/07/2020 vai retornar 60 (426/366), o dia 31/08/2020 vai retornar 91 (457/366) e assim vai... ate chegar no dia 30/06/2021 que vai retornar 28 de novo (760/366). Como a gente só quer pintar o mês onde o dia 366 caiu, dizemos para a formula retornar apenas os resultados onde o MOD() é menor ou igual a 31.

O resultado final é esse:

O importante aqui é entender a logica para poder adaptar ela conforme a nossa necessidade, e para sermos capazes de fazer manipulações parecidas em outros projetos. Espero que assim tenha ficado mais claro 😉

Abs!

Pedi uma explicação e ganhei uma aula! Que explicação bem feita, clareou tudo sobre a formula.

O passo a passo está bem didático e ajudou iluminou completamente a compreensão da fórmula, sem dúvida, aprendi uma nova lógica.

Fico muito grato pelo conhecimento compartilhado, um forte abraço e espero q receba tudo em dobro!