Cómo crear una dimensión de fechas en Power Query?

Hay varias alternativas para crear un calendario de fechas en Power BI. 

La dimension de fechas en Power BI se podría realizar mediante Excel, DAX o Power Query. Realizar la tabla de fechas en Excel no es recomendado, ya que siempre será estática. La tabla no irá incrementado las fechas según pasan los días. Por ejemplo, quizás queremos tener un calendario con las fechas hasta hoy, pero mañana queremos el calendario hasta mañana. Si queremos este tipo de calendario es mejor de realizarlo con DAX o Power Query. 

Entre DAX y Power Query (M) prácticamente no hay diferencia. Podemos llegar al mismo resultado con el mismo rendimiento. La única diferencia es que si realizamos el calendario con Power Query lo podremos llevar a un dataflow. Esto significa que se puede reutilizar fácilmente. Por esto considero que la mejor opción es realizar la tabla de fechas utilizando Power Query, en su caso Power Query Online (dataflows). A continuación se exponen los pasos para realizar la dimensión de fechas. 

Entráis en Power BI y os vais a ‘Transformar datos’ o Power Query.

A continuación, pincháis ‘Nuevo origen’ y ‘Consulta en blanco’.

  

Pincha en ‘Editor avanzado’ para poder cambiar el código de la nueva consulta que hemos añadido.

Ahora tenemos que sobrescribir el código con el script que tenemos abajo y presionamos listo. Este script crea una función en la cual podemos añadir la fecha de inicio y final de nuestra tabla de fechas.

let CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(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"}}),
  InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]), Int64.Type),
  InsertQuarter = Table.AddColumn(InsertYear, "Quarter Num", each Date.QuarterOfYear([Date]), Int64.Type),
  InsertCalendarQtr = Table.AddColumn(InsertQuarter, "Quarter Year", each "Q" & Number.ToText([Quarter Num]) & " " & Number.ToText([Year]), type text),
  InsertCalendarQtrOrder = Table.AddColumn(InsertCalendarQtr, "Quarter Year Order", each [Year] * 10 + [Quarter Num], Int64.Type),
  InsertMonth = Table.AddColumn(InsertCalendarQtrOrder, "Month Num", each Date.Month([Date]), Int64.Type),
  InsertMonthName = Table.AddColumn(InsertMonth, "Month Name", each Date.ToText([Date], "MMMM", null), type text),
  InsertMonthNameShort = Table.AddColumn(InsertMonthName, "Month Name Short", each Date.ToText([Date], "MMM", null), type text),
  InsertCalendarMonth = Table.AddColumn(InsertMonthNameShort, "Month Year", each (try(Text.Range([Month Name],0,3)) otherwise [Month Name]) & " " & Number.ToText([Year]), type text),
  InsertCalendarMonthOrder = Table.AddColumn(InsertCalendarMonth, "Month Year Order", each [Year] * 100 + [Month Num], Int64.Type),
  InsertWeek = Table.AddColumn(InsertCalendarMonthOrder, "Week Num", each Date.WeekOfYear([Date]), Int64.Type),
  InsertCalendarWk = Table.AddColumn(InsertWeek, "Week Year", each "W" & Number.ToText([Week Num]) & " " & Number.ToText([Year]), type text),
  InsertCalendarWkOrder = Table.AddColumn(InsertCalendarWk, "Week Year Order", each [Year] * 100 + [Week Num], Int64.Type),
  InsertWeekStarting = Table.AddColumn(InsertCalendarWkOrder, "Week Starting", each Date.StartOfWeek([Date]), type date),
  InsertWeekEnding = Table.AddColumn(InsertWeekStarting, "Week Ending", each Date.EndOfWeek([Date]), type date),
  InsertDay = Table.AddColumn(InsertWeekEnding, "Month Day Num", each Date.Day([Date]), Int64.Type),
  InsertDayInt = Table.AddColumn(InsertDay, "Date Int", each [Year] * 10000 + [Month Num] * 100 + [Month Day Num], Int64.Type),
  InsertDayWeek = Table.AddColumn(InsertDayInt, "Day Num Week", each Date.DayOfWeek([Date]) + 1, Int64.Type),
  InsertDayName = Table.AddColumn(InsertDayWeek, "Day Name", each Date.ToText([Date], "dddd", null), type text),
  InsertWeekend = Table.AddColumn(InsertDayName, "Weekend", each if [Day Num Week] = 6 then 1 else if [Day Num Week] = 7 then 1 else 0, Int64.Type),
  InsertDayNameShort = Table.AddColumn(InsertWeekend, "Day Name Short", each Date.ToText([Date], "ddd", null), type text),
  InsertIndex = Table.AddIndexColumn(InsertDayNameShort, "Index", 1, 1, Int64.Type),
  InsertDayOfYear = Table.AddColumn(InsertIndex, "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
  InsertYesterday = Table.AddColumn(InsertDayOfYear, "Yesterday?", each if Date.DayOfWeek(DateTime.Date(Date.AddDays(DateTime.LocalNow(), +1))) = 1 and [Date] = DateTime.Date(Date.AddDays(DateTime.LocalNow(), -3)) then 1 else if Date.DayOfWeek(DateTime.Date(Date.AddDays(DateTime.LocalNow(), +1))) <> 1 and [Date] = DateTime.Date(Date.AddDays(DateTime.LocalNow(), -1)) then 1 else 0, Int64.Type),
  InsertL7Days = Table.AddColumn(InsertYesterday, "L7Days?", each if [Date] >= DateTime.Date(Date.AddDays(DateTime.LocalNow(), -7)) then 1 else 0, Int64.Type),
  InsertCurrentDay = Table.AddColumn(InsertL7Days, "Current Day?", each Date.IsInCurrentDay([Date]), type logical),
  InsertCurrentWeek = Table.AddColumn(InsertCurrentDay, "Current Week?", each Date.IsInCurrentWeek([Date]), type logical),
  InsertCurrentMonth = Table.AddColumn(InsertCurrentWeek, "Current Month?", each Date.IsInCurrentMonth([Date]), type logical),
  InsertCurrentQuarter = Table.AddColumn(InsertCurrentMonth, "Current Quarter?", each Date.IsInCurrentQuarter([Date]), type logical),
  InsertCurrentYear = Table.AddColumn(InsertCurrentQuarter, "Current Year?", each Date.IsInCurrentYear([Date]), type logical),
  InsertL12M = Table.AddColumn(InsertCurrentYear, "L12M?", each Date.IsInPreviousNDays([Date], 365), type logical),
  InsertCompletedDay = Table.AddColumn(InsertL12M, "Completed Days", each if DateTime.Date(DateTime.LocalNow()) > [Date] then 1 else 0, Int64.Type),
  InsertCompletedWeek = Table.AddColumn(InsertCompletedDay, "Completed Weeks", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Date])) then 1 else if (Date.Year(DateTime.Date(DateTime.LocalNow())) < Date.Year([Date])) then 0 else if (Date.WeekOfYear(DateTime.Date(DateTime.LocalNow())) > Date.WeekOfYear([Date])) then 1 else 0, Int64.Type),
  InsertCompletedMonth = Table.AddColumn(InsertCompletedWeek, "Completed Months", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Date])) then 1 else if (Date.Year(DateTime.Date(DateTime.LocalNow())) < Date.Year([Date])) then 0 else if (Date.Month(DateTime.Date(DateTime.LocalNow())) > Date.Month([Date])) then 1 else 0, Int64.Type),
  InsertCompletedQuarter = Table.AddColumn(InsertCompletedMonth, "Completed Quarters", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Date])) then 1 else if (Date.Year(DateTime.Date(DateTime.LocalNow())) < Date.Year([Date])) then 0 else if (Date.QuarterOfYear(DateTime.Date(DateTime.LocalNow())) > Date.QuarterOfYear([Date])) then 1 else 0, Int64.Type),
  InsertCompletedYear = Table.AddColumn(InsertCompletedQuarter, "Completed Years", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Date])) then 1 else 0, Int64.Type),
  InsertYearsFromActualYear = Table.AddColumn(InsertCompletedYear, "YearsFromActualYear", each [Year] - Date.Year(DateTime.LocalNow()), Int64.Type),
  InsertMonthsFromActualMonth = Table.AddColumn(InsertYearsFromActualYear, "MonthsFromActualMonth", each if [Year]=Date.Year(DateTime.LocalNow()) then [Month Num]-Date.Month(DateTime.LocalNow())
else
[Month Num]-Date.Month(DateTime.LocalNow())+([YearsFromActualYear]*12), Int64.Type),
  InsertDaysFromToday = Table.AddColumn(InsertMonthsFromActualMonth, "DaysFromToday", each Duration.Days(Duration.From([Date]-DateTime.Date(Date.AddDays(DateTime.LocalNow(), 0)))), Int64.Type),
  InsertFiscalMonthNum = Table.AddColumn(InsertDaysFromToday, "Fiscal Month Num", each if [Month Num] > 3 then [Month Num]-3 else [Month Num]+9, Int64.Type),
  InsertFiscalQuarterNum = Table.AddColumn(InsertFiscalMonthNum, "Fiscal Quarter Num", each Number.RoundDown(([Fiscal Month Num]+2)/3), Int64.Type),
  InsertFiscalYear = Table.AddColumn(InsertFiscalQuarterNum, "Fiscal Year", each if [Month Num] <= 3 then Text.From([Year]-1)&"/"&Text.From([Year]-2000) else Text.From([Year])&"/"&Text.From([Year]-2000+1), type text),
  InsertFiscalYearNum = Table.AddColumn(InsertFiscalYear, "Fiscal Year Num", each Text.BeforeDelimiter([Fiscal Year], "/"), Int64.Type),
  #"InsertFiscalMM-YYYY" = Table.AddColumn(InsertFiscalYearNum, "Fiscal MM-YYYY", each Text.Combine({Text.PadStart(Text.From([Fiscal Month Num], "en-GB"), 2, "0"), "-", Text.From([Year], "en-GB")}), type text),
  #"InsertFiscalMM-YYYYOrder" = Table.AddColumn(#"InsertFiscalMM-YYYY", "Fiscal YYYY-MM Order", each [Year]*100+[Fiscal Month Num], Int64.Type),
  InsertFiscalYearsFromToday = Table.AddColumn(#"InsertFiscalMM-YYYYOrder", "FiscalYearsFromToday", each if Date.Month(DateTime.LocalNow()) <= 3 then Number.FromText([Fiscal Year Num])- Date.Year(DateTime.LocalNow()) - 1 else Number.FromText([Fiscal Year Num]) - Date.Year(DateTime.LocalNow()), Int64.Type),
  InsertCurrentFiscalMonthPeriod = Table.AddColumn(InsertFiscalYearsFromToday, "CurrentFiscalMonthPeriod", each if[Fiscal Month Num] < (if Date.Month(DateTime.LocalNow()) < 4 then Date.Month(DateTime.LocalNow()) + 9 else Date.Month(DateTime.LocalNow()) - 3) then 1 else 0, Int64.Type),
  #"Changed column type" = Table.TransformColumnTypes(InsertCurrentFiscalMonthPeriod, {{"Current Day?", Int64.Type}, {"Current Week?", Int64.Type}, {"Current Month?", Int64.Type}, {"Current Quarter?", Int64.Type}, {"Current Year?", Int64.Type}, {"L12M?", Int64.Type}})
in
  #"Changed column type"
in
CreateDateTable

Insertamos las fechas que queremos y le damos a invocar. Esto nos crea nuestra tabla de fechas. 

También podríamos insertar cualquier y cambiarlas después de manera que estas cambien automáticamente con las fechas que queremos. Por ejemplo, que siempre sea hasta la fecha de hoy o la fecha de hoy más 120 días en el futuro. Para hacer esto debemos cambiar el codigo desde la barra de formula o desde el editor avanzado.

A continuación, puedes encontrar ejemplos de código:

Hoy:

= Consulta1(#date(2019, 1, 1), DateTime.Date(Date.AddDays(DateTime.LocalNow(), 1)), null)

Hasta dentro de 120 días:

= Consulta1(#date(2019, 1, 1), DateTime.Date(Date.AddDays(DateTime.LocalNow(), 121)), null)

 

Hasta dentro de 6 meses:

= Consulta1(#date(2019, 1, 1), DateTime.Date(Date.AddMonths(DateTime.LocalNow(), 6)), null)

 

Hasta dentro de un año:

= Consulta1(#date(2019, 1, 1), DateTime.Date(Date.AddYears(DateTime.LocalNow(), 1)), null)