Calendário no Power BI

Quando fazemos análise de dados, rapidamente sentimos a necessidade de enquadrá-la num contexto de evolução temporal, por exemplo, ao longo de períodos de anos, meses ou até dias. Como podemos fazê-lo?

O Power BI tenta ajudar-nos com a opção “Time Intelligence” (auto date/time for new files). Conforme definição da Microsoft, quando esta opção está ligada, “por defeito é criada uma tabela de data escondida, para novos ficheiros, para cada campo no modelo que tenha um tipo de dados Date ou Date type”.

O Power BI tenta ajudar-nos com esta opção, mas por uma questão de performance deverá ser desligada (em Options -> Data Load -> Time intelligence -> desligar “Auto date/time for new files”).


Então qual é a alternativa?


Seguindo as boas praticas de modelação, a alternativa que deveremos escolher é a de criar e usar uma tabela dimensão Calendário que se liga e filtrará a nossa tabela de factos.


A tabela Calendário é uma tabela que contém a listagem de todos os dias distintos, entre uma data de início e de fim, mas o que a torna tão especial? Em síntese e conforme o artigo do Reza Rad “Do you need a date dimension“, uma tabela Calendário permite:
 

  • Capacidade de “slice and dice” por vários atributos de data, tais como semanas, semestre, dias do ano, tipo de dia, etc;
  • Consistência na análise e melhor performance;
  • Capacidade de fazer análise baseada em dias específicos, como feriados (como feriados, dias da semana, etc);
  • Certas ferramentas e cálculos são facilitados pelo uso da dimensão calendário.

Existem várias formas de obter uma tabela calendário (tanto em DAX como em Power Query), neste artigo vamos focar-nos na criação de uma tabela calendário no Power Query:

1- Copiar o seguinte código abaixo (código para obtenção calendário simplificado):
 

let

  P_Today = DateTime.LocalNow(),

  P_StartDate = #date(2018, 1, 1),

  P_EndDate = #date(Date.Year(P_Today),12,31),

  P_Culture = "en-EN",

  P_FirstDayOfWeek = 1,

  P_IsCarnivalHoliday = true,

  Holiday = if P_Culture = "pt-PT" then "Feriado" else "Holiday",

  Quarter = if P_Culture = "pt-PT" then "T" else "Q",

  Week = if P_Culture = "pt-PT" then "S" else "W",

  Weekend = if P_Culture = "pt-PT" then "Fim de Semana" else "Weekend",

  WorkDay = if P_Culture = "pt-PT" then "Dia Útil" else "Work Day",

  DayCount = Duration.Days(Duration.From(P_EndDate - P_StartDate)) + 1,

  Source = List.Dates(P_StartDate, DayCount, #duration(1, 0, 0, 0)),

  TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),

  ChangedType = Table.TransformColumnTypes(TableFromList, {{"Column1", type date}}),

  RenamedColumns = Table.RenameColumns(ChangedType, {{"Column1", "Date"}}),

  InsertId = Table.AddColumn(RenamedColumns, "DateId", each Date.Year([Date])*10000 + Date.Month([Date])*100 +Date.Day([Date])),

  InsertYear = Table.AddColumn(InsertId, "Year", each Date.Year([Date])),

  InsertQuarter = Table.AddColumn(InsertYear, "Quarter", each Date.QuarterOfYear([Date])),

  InsertSemester = Table.AddColumn(InsertQuarter, "Semester", each if [Quarter] < 3 then 1 else 2),

  InsertMonth = Table.AddColumn(InsertSemester, "Month (#)", each Date.Month([Date])),

  InsertWeek = Table.AddColumn(InsertMonth, "Week", each Date.WeekOfYear([Date], P_FirstDayOfWeek)),

  InsertDay = Table.AddColumn(InsertWeek, "Day", each Date.Day([Date])),

  InsertMonthName = Table.AddColumn(InsertDay, "Month (Long)", each Date.ToText([Date], "MMMM", P_Culture), type text),

  InsertShortMonthName = Table.AddColumn(InsertMonthName, "Month", each try(Text.Range([#"Month (Long)"],0,3)) otherwise [#"Month (Long)"]),

  InsertCalendarWeek = Table.AddColumn(InsertShortMonthName, "Week (Year)", each Week & Number.ToText([Week]) & " " & Number.ToText([Year])),

  InsertCalendarMonth = Table.AddColumn(InsertCalendarWeek, "Month (Year)", each [#"Month"] & " " & Number.ToText([Year])),

  InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "Quarter (Year)", each Quarter & Number.ToText([Quarter]) & " " & Number.ToText([Year])),

  InsertCalendarSem = Table.AddColumn(InsertCalendarQtr, "Semester (Year)", each "S" & Number.ToText([Semester]) & " " & Number.ToText([Year])),

  InsertDayWeek = Table.AddColumn(InsertCalendarSem, "Week Day (#)", each Date.DayOfWeek([Date], P_FirstDayOfWeek ) + 1),

  InsertDayName = Table.AddColumn(InsertDayWeek, "Week Day", each Date.ToText([Date], "dddd", P_Culture), type text),

  InsertWeekYear = Table.AddColumn(InsertDayName, "WeekYearId", each [Year] * 100 + [Week]),

  InsertMonthYear = Table.AddColumn(InsertWeekYear, "MonthYearId", each [Year] *100 + [#"Month (#)"]),

  // InsertStartWeek = Table.AddColumn(InsertWeekYear , "Start of Week", each Date.StartOfWeek([Date], P_FirstDayOfWeek), type date),

  // InsertEndWeek = Table.AddColumn(InsertStartWeek , "End of Week", each Date.EndOfWeek([Date], P_FirstDayOfWeek), type date),

  InsertQuarterYear = Table.AddColumn(InsertMonthYear, "QuarterYearId", each [Year] * 100 + [Quarter]),

  InsertSemesterYear = Table.AddColumn(InsertQuarterYear, "SemesterYearId", each [Year] * 100 + [Semester]),

  #"Capitalized Each Word" = Table.TransformColumns(InsertSemesterYear,{{"Month (Long)", Text.Proper}, {"Month", Text.Proper}, {"Month (Year)", Text.Proper}, {"Week Day", Text.Proper}}),

  #"Relative (Year)" = Table.AddColumn(#"Capitalized Each Word", "Year (Relative)", each [Year] - Date.Year(P_Today)),

  #"Relative (Month)" = Table.AddColumn(#"Relative (Year)", "Month (Relative)", each [#"Year (Relative)"] * 12 + ([#"Month (#)"] - Date.Month(P_Today))),

  #"Relative (Week)" = Table.AddColumn(#"Relative (Month)", "Week (Relative)", each Duration.TotalDays(DateTime.Date(Date.StartOfWeek([Date])) - DateTime.Date(Date.StartOfWeek(P_Today))) / 7),

  #"Relative (Day)" = Table.AddColumn(#"Relative (Week)", "Day (Relative)", each Duration.TotalDays([Date] - DateTime.Date(P_Today))),

  // MergedHolidays = Table.NestedJoin(#"Relative (Day)",{"Date"},GetHoliday(P_StartDate,P_EndDate,P_Culture, P_IsCarnivalHoliday),{"Date"},"Holidays",JoinKind.LeftOuter),

  // ExpandedHolidays = Table.ExpandTableColumn(MergedHolidays, "Holidays", {"Holiday"}, {"Holiday"}),

  // AddedWorkDay = Table.AddColumn(ExpandedHolidays, "Work Day", each if [Holiday] = null then (if [#"Week Day (#)"] > 5 then Weekend else WorkDay) else Holiday),

  #"Reordered Columns" = Table.ReorderColumns(#"Relative (Day)", {"Date", "Day", "Week Day (#)", "Week Day", "Week", "Month (Long)", "Month", "Month (#)", "Quarter", "Semester", "Year", "Week (Year)", "Month (Year)", "Quarter (Year)", "Semester (Year)", "WeekYearId", "MonthYearId", "QuarterYearId", "SemesterYearId", "Day (Relative)", "Week (Relative)", "Month (Relative)", "Year (Relative)"}),

  #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns", {{"Day", Int64.Type}, {"Week Day (#)", Int64.Type}, {"Week", Int64.Type}, {"Month (#)", Int64.Type}, {"Quarter", Int64.Type}, {"Semester", Int64.Type}, {"Year", Int64.Type}, {"Week (Year)", type text}, {"Quarter (Year)", type text}, {"Semester (Year)", type text}, {"WeekYearId", Int64.Type}, {"SemesterYearId", Int64.Type}, {"MonthYearId", Int64.Type}, {"QuarterYearId", Int64.Type}, {"Day (Relative)", Int64.Type}, {"Month (Relative)", Int64.Type}, {"Year (Relative)", Int64.Type}, {"DateId", Int64.Type}, {"Week (Relative)", Int64.Type}}),

  ColumnPT = Table.RenameColumns(#"Changed Type", {{"Date", "Data"}, {"DateId", "DataId"}, {"Day", "Dia"}, {"Week Day (#)", "Dia Semana (#)"}, {"Week Day", "Dia Semana"}, {"Week", "Semana"}, {"Month (Long)", "Mês (Extenso)"}, {"Month", "Mês"}, {"Month (#)", "Mês (#)"}, {"Quarter", "Trimestre"}, {"Semester", "Semestre"}, {"Year", "Ano"}, {"Week (Year)", "Semana (Ano)"}, {"Month (Year)", "Mês (Ano)"}, {"Quarter (Year)", "Trimestre (Ano)"}, {"Semester (Year)", "Semestre (Ano)"}, {"WeekYearId", "SemanaAnoId"}, {"MonthYearId", "MesAnoId"}, {"QuarterYearId", "TrimestreAnoId"}, {"SemesterYearId", "SemestreAnoId"}, {"Day (Relative)", "Dia (Relativo)"}, {"Month (Relative)", "Mês (Relativo)"}, {"Year (Relative)", "Ano (Relativo)"}, {"Week (Relative)", "Semana (Relativa)"}}),

  result = if P_Culture = "pt-PT" then ColumnPT else #"Changed Type"

in

  result

2- Abrir o Power Query: Separador “Home” -> Transform data

3- Criar “New blank query”: No Power Query -> Separador “Home” -> New Source -> Blank Query.

4- Clicar em Advanced Editor: Apagar o pedaço de código assinalado abaixo

5 – Fazer Paste do código previamente copiado no step 1 e fazer as seguintes alterações:
5.1- Mudar os steps P_StartDate e P_EndDate em conformidade (exemplo, se quiseres que a data de início seja 01/01/2020, no passo P_StartDate substituir o ano 2018 por 2020; se pretenderem que a data de fim seja o dia 31/12 do ano corrente (dinâmico) poderão deixar o P_EndDate inalterado; se preferirem, por exemplo, que a data de fim do calendário seja o fim do ano do ano anterior ao corrente, bastará inserir  “-1” depois de “Date.Year(P_Today)” – algo deste género: “Date.Year(P_Today)-1, 12, 31)”.

5.2– Um breve nota sobre o idioma: este calendário está preparado para a tradução para inglês ou português dos nomes das colunas da tabela calendário e das linhas, por exemplo, de “Weekend” para “Fim de Semana”, com a manipulação do passo “P_Culture”:

  • Se P_Culture = “en-EN”, todo o calendário fica em inglês;
  • Se P_Culture = “pt-PT”, todo o calendário fica em português.
     

6- Renomear o query para “Calendário“: Clicar duas vezes no query criado, renomear para “Calendário” ou “Calendar” e de seguida, separador Home -> “Close and Apply”:

7- Criar relação: Ligar a tabela Calendário à tabela de factos, pelo campo “Data” de cada uma das tabelas.



Estes são os passos para começar a usufruir de um calendário completo no vosso modelo, que permita todos o dinamismo de análise e performance pretendidos.

Num próximo artigo mostraremos como importar um calendário que também permita inserir feriados móveis ou feriados locais não contemplados.

Publicado por João Marcelino

BI Specialist

One thought on “Calendário no Power BI

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão /  Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão /  Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão /  Alterar )

Connecting to %s

%d bloggers like this: