Somatória de Ativos com o último saldo do mês

[PauloAguiar]

Olá,  Eu preciso fazer uma somatória pegando apenas o último dia útil do mês.

Imaginem que você precise calcular ações (investimentos), porém a coluna vem com as cotações diárias de todos os ativos. Como eu calcular o saldo final de cada mês?

Coloquei um simples exemplo no excel, espero não ter complicado no exemplo, rs!

Desde já agradeço.

Exemplo.xlsx

[Vitor Peralva]

Bom Dia, @PauloAguiar!

 

Inicialmente, lembro que você pode chegar a um mesmo resultado de várias maneiras, de modo que o método exposto abaixo não necessariamente é o melhor, mas o primeiro que veio a minha mente.

 

Estou partindo do pressuposto que a sua base só tem dias úteis e a respectiva cotação do dia do ativo.

Uma vez importado o arquivo para o Power Query, estando você somente com as linhas e colunas dos dados, vamos seguir os seguintes passos:

1) Selecione a coluna da Data e coloque o tipo como Data;

2) Vá na Guia Adicionar Coluna --> Coluna Personalizada e crie uma coluna, renomear para "Mês de Referência" e na fórmula usar o seguinte:

= Date.ToText([data], "MM/yyyy")

Com isto vamos ter uma coluna de Mês/Ano.

3) Vá na Guia Transformar --> Agrupar Por e na janela que abrir escolha Avançadas

Coloque na parte superior, de agrupamento, as colunas pelas quais quer agrupar.

Imagino que sejam "Mês de Referência" e Objeto.

Na parte inferior, peça o Máximo da Data.

Deve ficar mais ou menos assim:

 

 

O seu resultado agora traz a data máxima para cada ativo em cada mês.

 

Não temos aí a Posição.

Vamos resolver isto.

 

4) Vá na Guia Página Inicial --> Mesclar Consultas 

Na tabela de baixo, selecione a própria tabela.

Na parte superior, selecione a primeira coluna, segure o Ctrl e selecione as demais colunas.

Faça o mesmo embaixo na mesma ordem.

Você vai ter algo como:

 

Dê o OK.

 

5) Vá na Barra de Fórmulas e você vai encontrar a seguinte fórmula:

= Table.NestedJoin(#"Linhas Agrupadas", {"Mês de Referência", "Objeto", "Data"}, #"Linhas Agrupadas", {"Mês de Referência", "Objeto", "Data"}, "Linhas Agrupadas", JoinKind.LeftOuter)

 

Vamos fazer 2 (duas) alterações:

a) Você vai pegar o segundo #"Linhas Agrupadas" e substituir pela última etapa em que tinha o valor da posição, no caso, Personalização Adicionada (#"Personalização Adicionada");

b) No segundo grupo, vamos substituir Data por data, porque no seu original estava grafado assim.

Sua fórmula ficará:

= Table.NestedJoin(#"Linhas Agrupadas", {"Mês de Referência", "Objeto", "Data"}, #"Personalização Adicionada", {"Mês de Referência", "Objeto", "data"}, "Linhas Agrupadas", JoinKind.LeftOuter)

 

Veja que na coluna agrupada, ao clicar ao lado de Table, na parte em branco, na parte inferior é exibida a tabela a que ela se refere e nela temos a posição:

 

6) Realize a expansão e selecione apenas a posição e você deve chegar ao resultado esperado:

 

Bom, acredito que era este o seu objetivo.

Espero ter ajudado.

 

[PauloAguiar]

Muito Obrigado pelo seu tempo, Professor Peralva.

 

Apreciei sua dedicação para responder a minha pergunta. Mas infelizmente não atende o meu problema, eu tinha seguido essa linha, porém quando se tem um ativo que sai no meio do mês, por exemplo dia 15, ele soma junto com com todos os ativos do dia 29 por exemplo.

[Vitor Peralva]

Vamos lá, @PauloAguiar!

 

O problema, ao meu ver, está na definição da regra de negócio.

No seu arquivo de exemplo, você apresenta somente 3 (três) colunas: data, Objeto e Posição.

Na maioria dos meses, temos referências apenas ao primeiro dia.

É preciso se questionar sobre o que define que um ativo não está mais disponível, logo deve ser excluído do resultado final,  e esta regra de negócio que definirá a linha de raciocínio a ser adotada no Power Query.

Se puder esclarecer melhor a sua regra de negócio e fornecer um exemplo mais amplo, ficara mais fácil de lhe auxiliar.

 

[PauloAguiar]

Olá Professor, realmente eu não fui muito feliz no meu exemplo,rs! Agradeço sua dedicação a responder minha dúvida. No caso eu consegui resolver o problema via DAX. Usei a função LASTDATE usando a coluna de data da fato e joguei em um gráfico de barras.