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.