Ir para conteúdo
  • 0

Preencher metas para meses sem lançamento de meta


allisonresende
Ir para solução Solucionado por Vitor Peralva ,

Pergunta

  • Alunos

Olá, estou com o seguinte caso, existe o lançamento para meta no sistema mas nem sempre era usado ou tem o lançamento em uma data mas preciso que seja replicado para outros meses para ter a granularidade de meta por mês/veículos.

Podemos ter algumas situações:

-O veiculo não ter nem uma meta, dai não tem o que fazer, colocar Meta para todos mês = 0
-O veiculo ter meta em um mês anterior, replicar para os meses subsequente que não tem meta
-O veiculo não tem meta anterior usar a meta posterior replicando para os meses anteriores sem metas

Com estaria os dado no Power Query:

Veiculo  Data Meta
1 mar/22 45000
1 mai/22 40000
2 mar/22 50000
2 abr/22 45000
3 mai/22 0
4 jun/22 45000

 


Resultado desejado para período que o veiculo foi cadastrado ate a data atual:
 

Veiculo  Data Meta Meta atualiza
1 fev/22   45000
1 mar/22 45000 45000
1 abr/22   45000
1 mai/22 40000 40000
1 jun/22   40000
2 mar/22 50000 50000
2 abr/22 45000 45000
2 mai/22   45000
2 jun/22   45000
3 mai/22   0
3 jun/22   0
4 mai/22   45000
4 jun/22 45000 45000

 

Eu pensei em fazer no Power Query que que o Leo ensina sempre tentar fazer as correções no nível mais próximo a fonte de dados, no caso é uma consulta a um BD no select ja tento trazer somente uma linha por mês para cada veiculo.

Minha ideia e trazer no visual a meta tendo agrupamento por veiculo com os dados do jeito que estou falando daria para somar por total por veiculo ou por ano ou trimestre e taus e porde compara com o realizado conforme o mês/ano filtrado.

Meta Veiculos.xlsx

Link para o comentário
Compartilhar em outros sites

4 respostass a esta questão

Posts Recomendados

  • 1
  • Alunos
  • Solução

Boa Tarde, @allisonresende!

Pensando uma forma fácil de fazer para você:

1) Imagino que originalmente a tabela viesse assim:

image.png.f5196933b1efda2848a5668ca73db311.png

2) Vamos criar uma etapa para extrair a data máxima de toda a base:

Clique no fx na barra de fórmula e vai se criar uma etapa mais ou menos assim:

image.png.5a0b77ded2d1b11483eccbe5f5c603e2.png

Obs.: O meu ficou Colunas Removidas, porque exclui a sua coluna de Meta Atualizada.

Altere a barra de fórmula para:

image.png.de8d3ca8b500aa7fab482e2c30026d19.png

Para a sua base de exemplo, 01/06/2022 será retornado.

Renomeie o nome da estapa para DataMax.
3) Recuperar a consulta anterior.

Clique novamente no fx e na barra de fórmulas coloque = #"Colunas Removidas" (Vai ser o nome da etapa anterior a DataMax).

Voltará a ser exibida a tabela anterior.

Renomeie esta etapa para Base.

4) Vá na Guia Transformar e selecione Agrupar Por.

Preencha da seguinte forma:
image.png.6b4d1b92184d4e88a1ae11716609a473.png

 

5) Insira uma coluna personalizada (NumMeses) com o seguinte código:

let 

DtMax = DataMax,
DtMin = [DataMin],
MesMin = Date.Month(DtMin),
MesMax = Date.Month(DtMax),
AnoMin = Date.Year(DtMin),
AnoMax = Date.Year(DtMax),
CompAnos = AnoMax = AnoMin,
DifAnos = AnoMax - AnoMin - 1,
TotalMeses = 
if CompAnos then MesMax - MesMin 
else (DifAnos * 12) + MesMax + (12 - MesMin)

in 

TotalMeses

 

Seu resultado deve estar assim:
image.png.c489801996fea716489bcc399c9ca2eb.png

 

6) Adicione uma nova coluna personalizada (Personalizar) com o seguinte código:

{0 .. [NumMeses]}

 

Foi criada uma lista começando de zero e indo até a quantidade de meses que decorreram até a data máxima.

Expanda a lista.

7) Adicione mais uma coluna personalizada (Data) com o seguinte código:

Date.AddMonths([DataMin], [Personalizar])

😎 Mantenha somente a coluna de Veículo e Data.

9) Mescle a Consulta com ela mesma, por veículo e por data:

image.png.4ff0b24af58976308a50fd3f85b50c45.png

 

10) Vá até a barra de fórmulas e substitua a segunda tabela, no meu caso, #"Outras Colunas Removidas" por Base.

 

Antes:

image.thumb.png.91b0cb2a4583ad47ee5ff16d3c6ac2c2.png

Depois:

image.png.8f8f00968032faf13d9ad226e998ef32.png

 

11) Expanda somente a coluna de Meta.

image.png.629f0cfaa27cab1e6446d328f4b2fe50.png

 

12) Agrupe Por Veículo e peça todas as linhas:

image.png.21cd1bf7a37010c08c70fb85977488a9.png

 

image.png.3776cbcdc938b74b6e959245a923b6a0.png

 

13) Adicione a seguite coluna personalizada:

let 

PreencheBaixo = Table.FillDown([Linhas],{"Meta"}),
PreencheCima = Table.FillUp(PreencheBaixo,{"Meta"}),
EliminaNull = Table.ReplaceValue(PreencheCima,null,0,Replacer.ReplaceValue,{"Meta"})

in 

EliminaNull

 

Como resultado:

image.png.12b370559d1bed8f9feeeccc176986ee.png

 

14) Elimine as demais colunas, deixando somente a Personalizar:

15) Expanda a coluna Personalizar:

image.png.7f9cd90cf8f67ba9fe735fd653bca7e0.png

16) Defina os tipos e acredito que tem o resultado esperado.

 

Colocando o meu código inteiro como ficou:

let
    Fonte = Excel.Workbook(File.Contents("C:\Users\vitor\Downloads\49939684_MetaVeiculos.xlsx"), null, true),
    Plan1_Sheet = Fonte{[Item="Plan1",Kind="Sheet"]}[Data],
    #"Cabeçalhos Promovidos" = Table.PromoteHeaders(Plan1_Sheet, [PromoteAllScalars=true]),
    #"Tipo Alterado" = Table.TransformColumnTypes(#"Cabeçalhos Promovidos",{{"Veiculo ", Int64.Type}, {"Data", type date}, {"Meta", Int64.Type}, {"Meta atualiza", Int64.Type}}),
    #"Colunas Removidas" = Table.RemoveColumns(#"Tipo Alterado",{"Meta atualiza"}),
    DataMax = List.Max(#"Colunas Removidas"[Data]),
    Base = #"Colunas Removidas",
    #"Linhas Agrupadas" = Table.Group(Base, {"Veiculo "}, {{"DataMin", each List.Min([Data]), type nullable date}}),
    #"Personalização Adicionada" = Table.AddColumn(#"Linhas Agrupadas", "NumMeses", each let 

DtMax = DataMax,
DtMin = [DataMin],
MesMin = Date.Month(DtMin),
MesMax = Date.Month(DtMax),
AnoMin = Date.Year(DtMin),
AnoMax = Date.Year(DtMax),
CompAnos = AnoMax = AnoMin,
DifAnos = AnoMax - AnoMin - 1,
TotalMeses = 
if CompAnos then MesMax - MesMin 
else (DifAnos * 12) + MesMax + (12 - MesMin)

in 

TotalMeses),
    #"Personalização Adicionada1" = Table.AddColumn(#"Personalização Adicionada", "Personalizar", each {0 .. [NumMeses]}),
    #"Personalizar Expandido" = Table.ExpandListColumn(#"Personalização Adicionada1", "Personalizar"),
    #"Personalização Adicionada2" = Table.AddColumn(#"Personalizar Expandido", "Data", each Date.AddMonths([DataMin], [Personalizar])),
    #"Outras Colunas Removidas" = Table.SelectColumns(#"Personalização Adicionada2",{"Veiculo ", "Data"}),
    #"Consultas Mescladas" = Table.NestedJoin(#"Outras Colunas Removidas", {"Veiculo ", "Data"}, Base, {"Veiculo ", "Data"}, "Outras Colunas Removidas", JoinKind.LeftOuter),
    #"Outras Colunas Removidas Expandido" = Table.ExpandTableColumn(#"Consultas Mescladas", "Outras Colunas Removidas", {"Meta"}, {"Meta"}),
    #"Linhas Agrupadas1" = Table.Group(#"Outras Colunas Removidas Expandido", {"Veiculo "}, {{"Linhas", each _, type table [#"Veiculo "=nullable number, Data=date, Meta=nullable number]}}),
    #"Personalização Adicionada3" = Table.AddColumn(#"Linhas Agrupadas1", "Personalizar", each let 

PreencheBaixo = Table.FillDown([Linhas],{"Meta"}),
PreencheCima = Table.FillUp(PreencheBaixo,{"Meta"}),
EliminaNull = Table.ReplaceValue(PreencheCima,null,0,Replacer.ReplaceValue,{"Meta"})

in 

EliminaNull),
    #"Outras Colunas Removidas1" = Table.SelectColumns(#"Personalização Adicionada3",{"Personalizar"}),
    #"Personalizar Expandido1" = Table.ExpandTableColumn(#"Outras Colunas Removidas1", "Personalizar", {"Veiculo ", "Data", "Meta"}, {"Veiculo ", "Data", "Meta"})
in
    #"Personalizar Expandido1"

 

Espero que consiga ter ajudado a resolver.

 

 

image.png

Link para o comentário
Compartilhar em outros sites

  • 0
  • Alunos
1 hora atrás, allisonresende disse:

Olá, estou com o seguinte caso, existe o lançamento para meta no sistema mas nem sempre era usado ou tem o lançamento em uma data mas preciso que seja replicado para outros meses para ter a granularidade de meta por mês/veículos.

Podemos ter algumas situações:

-O veiculo não ter nem uma meta, dai não tem o que fazer, colocar Meta para todos mês = 0
-O veiculo ter meta em um mês anterior, replicar para os meses subsequente que não tem meta
-O veiculo não tem meta anterior usar a meta posterior replicando para os meses anteriores sem metas

Com estaria os dado no Power Query:

Veiculo  Data Meta
1 mar/22 45000
1 mai/22 40000
2 mar/22 50000
2 abr/22 45000
3 mai/22 0
4 jun/22 45000

 


Resultado desejado para período que o veiculo foi cadastrado ate a data atual:
 

Veiculo  Data Meta Meta atualiza
1 fev/22   45000
1 mar/22 45000 45000
1 abr/22   45000
1 mai/22 40000 40000
1 jun/22   40000
2 mar/22 50000 50000
2 abr/22 45000 45000
2 mai/22   45000
2 jun/22   45000
3 mai/22   0
3 jun/22   0
4 mai/22   45000
4 jun/22 45000 45000

 

Eu pensei em fazer no Power Query que que o Leo ensina sempre tentar fazer as correções no nível mais próximo a fonte de dados, no caso é uma consulta a um BD no select ja tento trazer somente uma linha por mês para cada veiculo.

Minha ideia e trazer no visual a meta tendo agrupamento por veiculo com os dados do jeito que estou falando daria para somar por total por veiculo ou por ano ou trimestre e taus e porde compara com o realizado conforme o mês/ano filtrado.

Meta Veiculos.xlsx 11 kB · 0 downloads

Cara Boa Tarde ao meu ver , você teria que estabelecer melhor os critérios , por exemplo :

em que situação devo usar cada um dos critérios citados por você , você tem alguma tabela aonde existam essa regras?

Caminhão x  não existe meta , não puxar nada .
Caminhão y puxar sempre a anterior e assim sucessivamente.

Os critérios que não ficaram claros para mim.

Link para o comentário
Compartilhar em outros sites

  • 0
  • Alunos
1 minuto atrás, Joao Barbosa disse:

Cara Boa Tarde ao meu ver , você teria que estabelecer melhor os critérios , por exemplo :

em que situação devo usar cada um dos critérios citados por você , você tem alguma tabela aonde existam essa regras?

Caminhão x  não existe meta , não puxar nada .
Caminhão y puxar sempre a anterior e assim sucessivamente.

Os critérios que não ficaram claros para mim.

Tem que validar o que foi puxado.

Link para o comentário
Compartilhar em outros sites

  • 0
  • Alunos

Outra coisa também seria preencher as as linha para todos os meses do meu modelos, para cada veiculo validando as sua data de cadastro.

Meu modelo pega dados a parti de 2020

Veiculo cadastrado  em janeiro de 2000. 
Porem esse só tem meta em março de 2022.

Tem que criar uma linha para cada mês dedes o inicio do modelo. 

Já um veiculo cadastrado em abril de 2021

Que tem meta somente em março de 2022.

Tem que criar uma linha para cada mês desde data do cadastro do veiculo.

Link para o comentário
Compartilhar em outros sites

Faça login para comentar

Você vai ser capaz de deixar um comentário após fazer o login



Entrar Agora
×
×
  • Criar Novo...