Tabela Calendário no Power BI com Ano Fiscal, Offset e Mês de Fechamento Personalizado

Leonardo Karpinski

Leonardo Karpinski

Mestre do Power BI, criador do Curso Express de Power BI e Curso Completo de Power BI. Formou mais de 16 mil alunos nos últimos anos e participou de projetos em grandes empresas nacionais e multinacionais.

O conteúdo de hoje será sobre criação da tabela dCalendario com Ano fiscal, Offset e inclusão de mês de fechamento personalizado.

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.

Antes de começar, vou te passar o link onde deixo as melhores lives do canal para você maratonar:

– Playlist com lives antigas: https://powerbiexperience.com/pt/playlist-lives-semanais/

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!

Base de dados

Trabalharemos uma base simples de vendas, veja:

Figura 1: Base fVendas

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.

Figura 2: Novo Parâmetro
Figura 2: Parâmetros

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):

Figura 3: Tabela Calendário no Power BI – forma dinâmica

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:

Figura 4: 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).
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

Kelly Silveira – Dúvida da Aluna do Curso Power BI Experience

Para checar como ficou a coluna Ano Fiscal que criamos ali no Power Query, vamos criar uma tabela:

Tabela Calendário com Ano Fiscal
Figura 5: Ano fiscal

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:

Trecho Linguagem M
Figura 6: Trecho do código

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.”

Link: Tabela Calendário com Trimestre Móvel – Power Query e Linguagem M – Comunidade Power BI Experience

Willian Oliveira – Dúvida do Aluno do Curso Power BI Experience

Basicamente eu criei colunas de offset na dCalendario para usar no Slicer (filtro). Veja que bacana ficou:

Tabela com Offset
Figura 7: Resultado Offset

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.

Tabela com Mês de fechamento personalizado
Figura 8: Mês/Ano 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.

Compartilhe este post:
Compartilhar no facebook
Compartilhar no linkedin
Compartilhar no twitter
Compartilhar no pinterest