Achou que Terça de Carnaval não iria ter live nem post, né?! O conteúdo de hoje será sobre criação da Tabela Calendário no Power BI. Te mostrarei como trabalhar com Ano fiscal, Offset e mês de fechamento personalizado no Power Query.
Se você que está lendo esse artigo é meu aluno, saiba a qualquer momento uma dúvida sua postada no Fórum de Alunos poderá ser tema de uma live nossa! Para isso acontecer, você precisar ser ativo na Comunidade, ou seja, não só perguntar mas também interagir com outros alunos, ok?!! Estamos combinados?!
Se você não é meu aluno, não se preocupe! Você consegue visualizar o fórum também. Veja aqui.
A ideia para essa live surgiu dessa dúvida aqui do Willian e da Kell:
– Tabela Calendário com Trimestre Móvel – Power Query e Linguagem M – Comunidade Power BI Experience e
– Calculo YTD Ano Fiscal – Linguagem DAX – Comunidade Power BI Experience
Bora começar!
Clique Aqui para dominar o Power Query, e aprenda a fazer as mais complexas conexões e transformações de dados com a Linguagem M.
Base de dados
Trabalharemos uma base simples de vendas, veja:
Veja que é uma tabela simples. Precisaremos nos preocupar apenas com a coluna Data Venda basicamente.
dCalendario no Power Query
Vamos criar os parâmetros AnoMin, AnoMax, MesInicioFiscal, MesFimFiscal. Para isso, basta acessar o Power Query e seguir: Página Inicial → Gerenciar Parâmetros → Novo parâmetro.
Veja, que criamos 4 parêmetros: AnoMin, AnoMax, MesInicioAnoFiscal e DiaInicioMes. Inseri valores manuais ali apenas para fins de teste, ok?!
Para agilizar, vou passar o código completo da dCalendario e em seguida vou pontuando o que foi feito com exemplos, beleza?!
Bom, agora, o próximo passo será utilizar aqueles parâmetros que criamos na dCalendario. Prepara o CTRL+C, CTRL+V para colar o monstrinho que vem agora:
let
DataInicio = #date(AnoMin, 1, 1),
DataFim = #date(AnoMax, 12, 31),
DataAtual = DateTime.Date(DateTime.FixedLocalNow()),
QtdDias = Duration.Days(Duration.From(DataFim - DataInicio)) + 1,
Fonte = List.Dates(DataInicio, QtdDias, #duration(1, 0, 0, 0)),
CriaTabela = Table.FromList(Fonte, Splitter.SplitByNothing()),
TipoAlterado1 = Table.TransformColumnTypes(CriaTabela, {{"Column1", type date}}),
RenomeiaColuna = Table.RenameColumns(TipoAlterado1, {{"Column1", "Data"}}),
InsereAno = Table.AddColumn(RenomeiaColuna, "Ano", each Date.Year([Data]), type number),
InsereTrimestre = Table.AddColumn(
InsereAno,
"Trimestre",
each Date.QuarterOfYear([Data]),
type number
),
InsereMesNo = Table.AddColumn(InsereTrimestre, "MesNo", each Date.Month([Data]), type number),
InsereDia = Table.AddColumn(InsereMesNo, "Dia", each Date.Day([Data]), type number),
InsereAnoMesDia = Table.AddColumn(
InsereDia,
"AnoMesDia",
each [Ano] * 10000 + [MesNo] * 100 + [Dia],
type number
),
InsereMes = Table.AddColumn(InsereAnoMesDia, "Mês", each Date.ToText([Data], "MMMM"), type text),
InsereMesAno = Table.AddColumn(
InsereMes,
"Mês/Ano",
each (try (Text.Range([Mês], 0, 3)) otherwise [Mês]) & "/" & Number.ToText([Ano]),
type text
),
InsereTrimestreAno = Table.AddColumn(
InsereMesAno,
"Trimestre/Ano",
each "T" & Number.ToText([Trimestre]) & "/" & Number.ToText([Ano]),
type text
),
InsereDiaSemana = Table.AddColumn(
InsereTrimestreAno,
"Dia da Semana",
each Date.DayOfWeek([Data]),
Int64.Type
),
InsereNomeDia = Table.AddColumn(
InsereDiaSemana,
"Nome do Dia",
each Date.ToText([Data], "dddd"),
type text
),
InsereSemanaDoAno = Table.AddColumn(
InsereNomeDia,
"Semana do Ano",
each Date.WeekOfYear([Data]),
type number
),
InsereSemanaAno = Table.AddColumn(
InsereSemanaDoAno,
"Semana/Ano",
each "S" & Text.From([Semana do Ano]) & "/" & Text.From([Ano]),
type text
),
InsereAnoSemana = Table.AddColumn(
InsereSemanaAno,
"AnoSemana",
each [Ano] * 100 + [Semana do Ano],
type number
),
InsereAnoMes = Table.AddColumn(InsereAnoSemana, "AnoMes", each [Ano] * 100 + [MesNo], type number),
InsereAnoTrimestre = Table.AddColumn(
InsereAnoMes,
"AnoTrimestre",
each [Ano] * 100 + [Trimestre],
type number
),
InsereAnoFiscal = Table.AddColumn(
InsereAnoTrimestre,
"Ano Fiscal",
each (
if MesInicioAnoFiscal = 1 then
Text.From([Ano]) & "/" & Text.From([Ano] + 1)
else if [MesNo] >= MesInicioAnoFiscal then
Text.From([Ano]) & "/" & Text.From([Ano] + 1)
else
Text.From([Ano] - 1) & "/" & Text.From([Ano])
),
type text
),
InsereTrimestreFiscal = Table.AddColumn(
InsereAnoFiscal,
"Trimestre Fiscal",
each "FT"
& Text.From(
Number.RoundUp(Date.Month(Date.AddMonths([Data], - (MesInicioAnoFiscal - 1))) / 3)
),
type text
),
InsereMesFiscal = Table.AddColumn(
InsereTrimestreFiscal,
"Mês Fiscal",
each
if [MesNo] >= MesInicioAnoFiscal then
[MesNo] - (MesInicioAnoFiscal - 1)
else
[MesNo] + (12 - MesInicioAnoFiscal + 1),
type text
),
InsereFlagFuturo = Table.AddColumn(
InsereMesFiscal,
"Futuro",
each not ([Data] <= Date.From(DataAtual)),
type logical
),
InsereFlagDiaUtil = Table.AddColumn(
InsereFlagFuturo,
"Dia Útil",
each if [Dia da Semana] = 5 then false else if [Dia da Semana] = 6 then false else true,
type logical
),
InsereOffsetDia = Table.AddColumn(
InsereFlagDiaUtil,
"Offset Dia",
each Number.From([Data] - DataAtual),
type number
),
InsereOffsetSemana = Table.AddColumn(
InsereOffsetDia,
"Offset Semana",
each (
Number.From(Date.StartOfWeek([Data])) - Number.From(Date.StartOfWeek(DateTime.LocalNow()))
)
/ 7,
type number
),
InsereOffsetMes = Table.AddColumn(
InsereOffsetSemana,
"Offset Mês",
each ((12 * Date.Year([Data])) + Date.Month([Data]))
- ((12 * Date.Year(Date.From(DataAtual))) + Date.Month(Date.From(DataAtual))),
type number
),
InsereOffsetTrimestre = Table.AddColumn(
InsereOffsetMes,
"Offset Trimestre",
each ((4 * Date.Year([Data])) + Date.QuarterOfYear([Data]))
- ((4 * Date.Year(Date.From(DataAtual))) + Date.QuarterOfYear(Date.From(DataAtual))),
type number
),
InsereOffsetAno = Table.AddColumn(
InsereOffsetTrimestre,
"Offset Ano",
each Date.Year([Data]) - Date.Year(Date.From(DataAtual)),
type number
),
IdentificaDiaAtual = Table.SelectRows(InsereOffsetAno, each ([Data] = DataAtual)),
AnoFiscalAtual = IdentificaDiaAtual{0}[Ano Fiscal],
InsereOffsetAnoFiscal = Table.AddColumn(
InsereOffsetAno,
"Offset Ano Fiscal",
each Number.From(Text.Range([Ano Fiscal], 2, 2)) - Number.From(Text.Range(AnoFiscalAtual, 2, 2)),
type number
),
InsereSemanaCompleta = Table.AddColumn(
InsereOffsetAnoFiscal,
"Semana Completa",
each Date.EndOfWeek([Data]) < Date.From(Date.EndOfWeek(DataAtual)),
type logical
),
InsereMesCompleto = Table.AddColumn(
InsereSemanaCompleta,
"Mês Completo",
each Date.EndOfMonth([Data]) < Date.From(Date.EndOfMonth(DataAtual)),
type logical
),
InsereTrimestreCompleto = Table.AddColumn(
InsereMesCompleto,
"Trimestre Completo",
each Date.EndOfQuarter([Data]) < Date.From(Date.EndOfQuarter(DataAtual)),
type logical
),
IdentificaOffsetMes = Table.AddColumn(
InsereTrimestreCompleto,
"MesOffsetFechamento",
each
if DiaInicioMes = 1 then
[Offset Mês]
else if DiaInicioMes > [Dia] then
[Offset Mês]
else
[Offset Mês] + 1
),
RemoveDuplicados = Table.Distinct(IdentificaOffsetMes, {"Offset Mês"}),
MesclaConsultas = Table.NestedJoin(
IdentificaOffsetMes,
{"MesOffsetFechamento"},
RemoveDuplicados,
{"Offset Mês"},
"Join",
JoinKind.LeftOuter
),
#"Join Expandido" = Table.ExpandTableColumn(
MesclaConsultas,
"Join",
{"Mês/Ano", "AnoMes"},
{"Mês/Ano Fechamento", "AnoMes Fechamento"}
),
#"Colunas Removidas" = Table.RemoveColumns(#"Join Expandido", {"MesOffsetFechamento"}),
#"Tipo Alterado" = Table.TransformColumnTypes(
#"Colunas Removidas",
{
{"Data", type date},
{"Ano", Int64.Type},
{"Trimestre", Int64.Type},
{"MesNo", Int64.Type},
{"Dia", Int64.Type},
{"AnoMesDia", Int64.Type},
{"Mês", type text},
{"Mês/Ano", type text},
{"Trimestre/Ano", type text},
{"Dia da Semana", Int64.Type},
{"Nome do Dia", type text},
{"Semana do Ano", Int64.Type},
{"Semana/Ano", type text},
{"AnoSemana", Int64.Type},
{"AnoMes", Int64.Type},
{"AnoTrimestre", Int64.Type},
{"Ano Fiscal", type text},
{"Trimestre Fiscal", type text},
{"Mês Fiscal", Int64.Type},
{"Futuro", type logical},
{"Dia Útil", type logical},
{"Offset Dia", Int64.Type},
{"Offset Semana", Int64.Type},
{"Offset Mês", Int64.Type},
{"Offset Trimestre", Int64.Type},
{"Offset Ano", Int64.Type},
{"Offset Ano Fiscal", Int64.Type},
{"Semana Completa", type logical},
{"Mês Completo", type logical},
{"Trimestre Completo", type logical},
{"Mês/Ano Fechamento", type text},
{"AnoMes Fechamento", Int64.Type}
}
)
in
#"Tipo Alterado"
Repare que esse código completo não possui apenas a solução das dúvidas que elegemos para mostrar mas também várias outras coisas legais de se ter numa tabela Calendário.
Caso precise obter a dCalendario de forma dinâmica, com base na data mínima e máxima da tabela fVendas, você pode realizar algumas alterações no início do código. Veja o trecho que você precisaria incluir/substituir em verde (que está comentado):
Resumindo, você precisaria incluir o código entre as linhas 2 e 5 e remover as linhas 6 e 7. Entendeu?!
Vamos dar olhada em como ficou nossa tabela dCalendario:
Após fechar e aplicar, bora criar uma tabela para checar se realmente está OK.
Ano fiscal
Nosso primeiro desafio:
“Na empresa onde trabalho consideramos o intervalo de Mar – Fev como ano fiscal (atualmente estamos 20/21).
Kelly Silveira – Dúvida da Aluna do Curso Power BI Experience
Estou desenvolvendo um projeto para um departamento, fiz uma medida utilizando datesytd (até deu certo até o mês de dezembro/20), agora quando incluímos o mês de janeiro/21, ele não traz o valor.”
Link: Calculo YTD Ano Fiscal – Linguagem DAX – Comunidade Power BI Experience
Para checar como ficou a coluna Ano Fiscal que criamos ali no Power Query, vamos criar uma tabela:
Pronto, agora, você consegue usar um filtro e selecionar apenas o Ano fiscal de interesse com base nas regras da sua empresa.
O trecho do código onde criamos a coluna de Ano Fiscal é esse daqui:
Vamos para o próximo desafio!
Offset
A dúvida do nosso aluno foi essa:
“Como faço pra criar uma tabela calendário com uma dimensão de trimestre móvel? Exemplo: Se estou no mês 5, meu trimestre móvel são os meses 3, 4 e 5. Quando virar para o mês 6, meu trimestre móvel são os meses 4, 5 e 6.”
Willian Oliveira – Dúvida do Aluno do Curso Power BI Experience
Link: Tabela Calendário com Trimestre Móvel – Power Query e Linguagem M – Comunidade Power BI Experience
Basicamente eu criei colunas de offset na dCalendario para usar no Slicer (filtro). Veja que bacana ficou:
Vamos interpretar o que temos aqui em cima:
Suponha que hoje estamos na semana 10 do ano de 2021, em Março.
Quando colocamos Offset Ano: de -2 a -1, significa que estamos filtrando a tabela para que nela apareça apenas dados de 2020 e 2019 (representado pelos valores -1 e -2 respectivamente).
O mesmo pode ser feito com os demais filtros:
– Offset Mês: de -35 a 10. Equivale ao 10º mês após a data atual e a 35 meses anteriores a essa data atual. Ou seja, obteremos dados entre esses dois períodos.
– Offset semana: de 0 a -36 . Representa 36 semanas anteriores a data atual.
Resumo da opera: o 0 indica a data atual (hoje) e tudo que vem depois representa o valor positivo e o passado é representado pelo número negativo.
Fala sério! Incrível né?!
Mês de fechamento personalizado
Agora, pra fechar! Imagine que na sua empresa o dia de fechamento é 25 (ou seja, não é o último dia do mês)… Veja que a nossa Master dCalendario também tem uma coluna com o Mês/Ano de fechamento.
Veja que dia 26 de Janeiro já começa o fev/2019. Bacana né?!
Pode não ser uma necessidade sua agora, mas com certeza um dia será. Então, trate de salvar essa dCalendario em algum cantinho para consultar sempre que precisar, hein?! Ou pode voltar aqui no blog também! Você que escolhe! Essa tabela é bem rica e tem muito mais coisas do que te mostrei aqui! Ela está bem completa!
Ah! Vou deixar umas sugestões de artigos relacionados a Power Query que podem complementar o material de hoje e te mostrar o quão poderosa é essa ferramenta:
Utilizando o PODER do Power Query para Estruturar Arquivos Complexos – Power BI Experience
Bom, agora sim, finalizamos por hoje! Espero que tenha gostado da criação da Tabela Calendário que criamos no Power BI.
Abraços,
Leonardo.
