Dica do LuckyTemplates: como comparar os primeiros N dias úteis

Para esta postagem, gostaria de falar sobre uma dica recente do LuckyTemplates que criei para um membro do . A pergunta tratava de um padrão útil em termos de olhar para os primeiros N dias úteis ou primeiros N dias faturáveis ​​de um determinado mês em relação ao mesmo período no mês anterior. Você pode assistir ao vídeo completo deste tutorial na parte inferior deste blog.

TJ Henneman queria comparar os primeiros 5 dias faturáveis ​​do mês atual com os primeiros 5 dias faturáveis ​​do mês anterior e, em seguida, no dia 10, dia 15 e dia 20. Observe que ele deseja consultar apenas os dias faturáveis, então isso não inclui fins de semana e feriados. Você pode conferir o post dele .

Eu acho que há uma solução de consulta de energia realmente interessante aqui. Também trabalhei com uma solução DAX interessante para esse problema. Vou falar sobre o último em outro tutorial.

Também quero orientá-lo em uma técnica de relatório recente que usei para o LuckyTemplates Challenge #16. Usei um indicador KPI de rolagem que funcionará muito bem para esse problema específico.

Mas para esta dica do LuckyTemplates, abordarei a solução de consulta de energia. Vamos pular para o LuckyTemplates e dar uma olhada em nossos dados. Para este, simulei os dados usando a ferramenta prática de conjunto de dados que reunimos no LuckyTemplates, que você pode usar gratuitamente. Isso é ótimo para reunir um conjunto de dados de esquema em estrela básico e para testar soluções.

Índice

• Nosso modelo de dados
• Criando uma tabela duplicada
• Mesclando a Tabela de Datas
• Criando uma coluna personalizada
• Criando um parâmetro hipotético
• Criando uma medida para o número de dias úteis
• Conclusão

Nosso modelo de dados

Nosso modelo de dados usa nossa tabela de data estendida mais uma série de tabelas de dimensão. Também temos uma tabela de vendas com três anos de dados parciais no primeiro ano, dados completos no segundo ano e dados parciais no terceiro ano. Por fim, também temos uma tabela de Medidas .

Eu também modifiquei ligeiramente este conjunto de dados. Eu trouxe uma tabela de feriados e vinculei a tabela de datas estendidas para que ela possa calcular corretamente os feriados que iremos remover nesta análise. Se você não tem certeza de como fazer isso, confira este tutorial .

Em seguida, filtrei o primeiro mês parcial no conjunto de dados porque esse mês tinha apenas alguns dias de dados e não tinha os dias úteis de que precisamos. Observe que estamos começando com o primeiro mês completo; mas fora isso, este será seu conjunto de dados padrão. Então, vamos pular para a consulta de energia e começar a trabalhar nesta dica do LuckyTemplates.

Se dermos uma olhada em nossos dados, veremos que temos pedidos diferentes em um determinado dia. O objetivo é numerar tudo o que começa com 1º de abril como nosso primeiro dia útil, 2 de abril como nosso segundo dia útil, 3 de abril como nosso terceiro dia útil e assim por diante. Vamos numerar isso de 1 a N dias úteis para cada mês, tirando os finais de semana e feriados.

Criando uma tabela duplicada

Para começar, precisamos criar uma duplicata dessa tabela para que possamos agregá-la ao Total de vendas e trazê-la para o nível de observação individual. Vamos chamar essa tabela duplicada de Sales Aggregated .

Você deve ter notado que duplicamos a tabela Sales em vez de apenas fazer referência a ela. Neste caso, vamos unir esta tabela com a tabela Sales original. Se o referenciarmos, isso causará problemas quando unirmos os dois.

A primeira coisa a fazer é usar um Group By na coluna OrderDate . Clique no botão Avançado porque ele nos dará uma visão completa do que estamos fazendo aqui. Colocaremos Total Day Sales como nosso novo nome de coluna e será a soma do total de nossa linha.

Agora temos cada data (primeira coluna) e o total de vendas desse dia (segunda coluna).

Mesclando a Tabela de Datas

A próxima etapa nesta dica do LuckyTemplates é mesclar isso com nossa tabela Datas para descobrir quais desses dias são dias úteis e quais não são. Podemos agrupar por novamente, dividi-los e numerá-los adequadamente.

Vamos mesclar nossa tabela Dates e vincular OrderDate a Date . Podemos ver na parte inferior que a seleção corresponde a cada linha da tabela, como deveria.

O campo no qual estamos interessados ​​principalmente é IsBusinessDay .

Este é o campo principal no qual agruparemos e, em seguida, usaremos os campos Mês e Ano e MêsnAno .

Também precisamos verificar DayofWeekName para garantir que estamos filtrando os dias adequados.

Assim que clicarmos no botão OK , ele nos dará exatamente o que precisamos.

Também precisamos garantir que a data do pedido seja classificada em ordem crescente.

Em seguida, voltamos e criamos um segundo Group By .

Desta vez, clicaremos no botão Avançado , agruparemos em Mês e Ano e adicionaremos um segundo agrupamento.

O segundo agrupamento é para IsBusinessDay para que possamos numerar aqueles em que os dias úteis são verdadeiros.

Em seguida, criaremos uma função AllRows para que ela não seja agregada e possamos voltar ao nível original de granularidade.

Isso deve nos fornecer tabelas aninhadas onde podemos ver uma tabela para true e uma tabela para false para cada mês.

Criando uma coluna personalizada

Em seguida, precisamos criar uma coluna personalizada para adicionar a contagem necessária para os dias úteis.

Vamos usar uma função chamada Table.AddIndexColumn , que irá operar na tabela AllRows , e vamos chamá-la de Day Index . Queremos que o índice comece com 1 e aumente em 1 a cada novo dia útil.

Em seguida, podemos remover as três primeiras colunas e expandir nossa quarta coluna personalizada.

Se olharmos o resultado, ele nos dá o índice de dias, que conta os dias úteis (verdadeiro) e depois conta os dias que são finais de semana e feriados de cada mês (falso). Em seguida, temos que verificar se eles estão em seu tipo de campo apropriado.

Voltemos à nossa tabela de Vendas. Iremos mesclar a tabela Aggregate que acabamos de criar com nossa tabela Sales.

Vamos mesclar aqueles no OrderDate . Você pode ver na parte inferior que corresponde a todas as linhas da primeira tabela.

Quando expandirmos a coluna Sales Agg, selecione os novos campos que acabamos de adicionar: as colunas Month & Year , DayOfWeekName , MonthnYear , IsBusinessDay e DayIndex .

Vamos também limpar nossos dados antes de clicar em Fechar e Aplicar . Vamos mover a tabela Sales Agg em nossa seção Data Prep e, em seguida, descarregar essa tabela, pois não precisaremos dela no modelo de dados. Em seguida, clique em Fechar e Aplicar .

Nosso trabalho de preparação de consulta de energia está concluído.

Criando um parâmetro hipotético

Lembre-se de que na tarefa original queremos variar o número de dias que examinamos de 5 a 10 e de 15 a 20. Acho que a melhor maneira de fazer isso é com um novo parâmetro hipotético .

Chamaremos o parâmetro hipotético de Primeiros N Dias Úteis e seu tipo de dados será um número inteiro. O mínimo é definido para um incremento de 1 e um máximo de 20. Em seguida, o padrão será 5, que é o primeiro valor que o usuário deseja.

Agora temos um slicer que cria automaticamente a medida de colheita para capturar o valor do slicer.

Vamos criar uma tabela com nossos resultados. Vamos colocar nossa dimensão Mês e Ano na tela e classificá-la por Mês e Ano .

Assim que tivermos esse campo devidamente classificado, podemos simplesmente descartar a medida Total Sales e expandi-la.

Criando uma medida para o número de dias úteis

Precisamos criar mais uma medida que analise o valor do parâmetro do controle deslizante e calcule apenas o número de dias úteis.

Chamaremos essa nova medida de Total Sales N Bus Days . Começaremos com a função , já que definitivamente estamos mudando o contexto, e com nossa medida Total Sales. Vamos filtrar isso com o da nossa tabela Sales.

O próximo passo é escrever as condições que queremos levar em conta para o controle deslizante. Temos nosso índice de dias de vendas que criamos no Power Query e queremos que seja menor ou igual ao valor dos primeiros N dias úteis, que é o valor colhido do slicer.

Por exemplo, se o controle deslizante estiver em 5, queremos todos os dias da tabela Vendas em que o índice do dia seja menor ou igual a 5.

E então queremos tirar os dias não úteis. Faremos referência à tabela Date e usaremos o campo IsBusinessDay. Veremos apenas o resultado que for VERDADEIRO.

Por fim, traga o contexto de volta para Mês e Ano e feche a medida. Devemos ser bons para ir neste momento.

Vamos colocar a nova medida em nossa tabela.

Podemos levar o controle deslizante até a marca de 10 dias e a tabela calculará dinamicamente na marca de 10 dias.

Conclusão

Nesta dica do LuckyTemplates, falamos sobre como fazer uma comparação igual a igual dinamicamente usando uma solução Power Query. Podemos colocar isso em um gráfico de barras ou gráfico de linhas.

Em alguns de nossos próximos tutoriais, veremos a solução DAX e implementaremos a visualização do scroller de KPI.

Se você gostou do conteúdo abordado neste tutorial específico, não se esqueça de se inscrever no canal de TV LuckyTemplates .