Selector de fechas dinámico en Power BI

15 de mayo de 2024

s de lanàlisi

En el mundo del análisis de datos con Power BI, la selección eficiente de fechas es crucial para una visualización y manipulación de datos efectiva. Sin embargo, Power BI carece de un selector de fechas integrado que cumpla con las expectativas de usabilidad de la mayoría de los usuarios. Este tutorial proporciona una guía paso a paso para crear un selector de fechas personalizado en Power BI, superando las limitaciones del selector predeterminado.

1.- Tabla de Última Actualización: Para comenzar, generamos una tabla en el editor de Power Query que captura la última fecha y hora de actualización del informe. Es esencial considerar el huso horario local para asegurar la precisión al publicar en Power BI Service. A continuación, se presenta el código de Power Query necesario para esta tarea:

let

date = DateTime.Date(DateTimeZone.SwitchZone(DateTimeZone.UtcNow(),+1,0)),
time = DateTime.Time(DateTimeZone.SwitchZone(DateTimeZone.UtcNow(),+1,0)),
//+1 hora para obtener hora de invierno de España respecto a hora UTC.


firstSundayOfNovember = Date.StartOfWeek(#date(Date.Year(date), 11, 7), Day.Sunday),
SecondSundayOfMarch = Date.StartOfWeek(#date(Date.Year(date), 3, 14), Day.Sunday),


isSummerTime =  (date = SecondSundayOfMarch and time >= #time(1,0,0))
          or
    (date > SecondSundayOfMarch and date < firstSundayOfNovember) 
    or 
    (date = firstSundayOfNovember and time >= #time(1,0,0)),


timeZone = (Number.From(isSummerTime))*1, 
//sumamos una hora para el horario de verano en el huso horario de España


ltime = 
            DateTime.From(date) 
            + #duration(0,Time.Hour(time),Time.Minute(time),Time.Second(time))  
            + #duration(0, timeZone, 0, 0),
    #"Convertida en tabla" = #table(1, {{ltime}}),
    #"Columnas con nombre cambiado" = Table.RenameColumns(#"Convertida en tabla",{{"Column1", "UltimaActualización"}}),
    #"Tipo cambiado" = Table.TransformColumnTypes(#"Columnas con nombre cambiado",{{"UltimaActualización", type datetime}})
in
    #"Tipo cambiado"

2.- Creación de un Calendario Independiente: El siguiente paso es crear un calendario en Power Query que se utilizará en las medidas del informe2. Este calendario debe permanecer sin vincular a otras tablas en el modelo. El código M para la creación del calendario es el siguiente:

let
    Source = #date(2018, 1, 1), 
    FechaFinal = #date(Date.Year(DateTime.LocalNow()),12,31),
    ListaInicioFinal = {Number.From(Source)..Number.From(FechaFinal)},
    #"Converted to Table" = Table.FromList(ListaInicioFinal, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Fecha"}}),
    #"Added Custom Column" = Table.AddColumn(#"Renamed Columns", "Custom", each Text.Combine({Date.ToText([Fecha], "yyyy"), Date.ToText([Fecha], "MM"), Date.ToText([Fecha], "dd")}), type text),
    #"Inserted Year" = Table.AddColumn(#"Added Custom Column", "Year", each Date.Year([Fecha]), Int64.Type),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Year", "Quarter", each Date.QuarterOfYear([Fecha]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Quarter", "Month", each Date.Month([Fecha]), Int64.Type),
    #"Inserted Day" = Table.AddColumn(#"Inserted Month", "Day", each Date.Day([Fecha]), Int64.Type),
    #"Inserted Merged Column" = Table.AddColumn(#"Inserted Day", "Merged", each Text.Combine({"T", Text.From([Quarter], "es-MX")}), type text),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Merged Column", "Month Name", each Date.MonthName([Fecha]), type text),
    #"Inserted First Characters" = Table.AddColumn(#"Inserted Month Name", "First Characters", each Text.Start([Month Name], 3), type text),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted First Characters", "Day of Week", each Date.DayOfWeek([Fecha]), Int64.Type),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Day of Week", "Week of Year", each Date.WeekOfYear([Fecha]), Int64.Type),
    #"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([Fecha]), Int64.Type),
    #"Inserted End of Week" = Table.AddColumn(#"Inserted Week of Month", "End of Week", each Date.EndOfWeek([Fecha],Day.Monday), type date),
    #"Inserted Day of Year" = Table.AddColumn(#"Inserted End of Week", "Day of Year", each Date.DayOfYear([Fecha]), Int64.Type),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Year", "Day Name", each Date.DayOfWeekName([Fecha]), type text),
    #"Inserted First Characters1" = Table.AddColumn(#"Inserted Day Name", "First Characters.1", each Text.Start([Day Name], 3), type text),
    #"Inserted Merged Column1" = Table.AddColumn(#"Inserted First Characters1", "Merged.1", each Text.Combine({Text.From([Year], "es-MX"), [Merged]}, "/"), type text),
    #"Inserted First Characters2" = Table.AddColumn(#"Inserted Merged Column1", "First Characters.2", each Text.Start([Custom], 6), type text),
    #"Split Column by Position" = Table.SplitColumn(#"Inserted First Characters2", "First Characters.2", Splitter.SplitTextByPositions({0, 4}, false), {"First Characters.2.1", "First Characters.2.2"}),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Position",{"First Characters.2.1", "First Characters.2.2"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Merged.3"),
    #"Inserted Merged Column2" = Table.AddColumn(#"Merged Columns", "Merged.2", each Text.Combine({Text.From([Year], "es-MX"), "/", [First Characters]}), type text),
    #"Replaced Value" = Table.ReplaceValue(#"Inserted Merged Column2",0,7,Replacer.ReplaceValue,{"Day of Week"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Replaced Value",{{"Custom", "FechaSK"}, {"Year", "#Año"}, {"Quarter", "#Trimestre"}, {"Month", "#Mes"}, {"Day", "#Día"}, {"Merged", "Trimestre"}, {"Month Name", "Mes"}, {"First Characters", "MesCorto"}, {"Day of Week", "#DíaSemana"}, {"Week of Year", "#SemanaAño"}, {"Week of Month", "#SemanaMes"}, {"End of Week", "CierreSemana"}, {"Day of Year", "#DíaAño"}, {"Day Name", "Día"}, {"First Characters.1", "DíaCorto"}, {"Merged.1", "AñoTrimestre"}, {"Merged.3", "Año#Mes"}, {"Merged.2", "AñoMesCorto"}}),
    #"Tipo cambiado" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Fecha", type date}}),
    #"Columna duplicada" = Table.AddColumn(#"Tipo cambiado", "Fecha - Copia", each [Fecha], type date),
    #"Columnas quitadas" = Table.RemoveColumns(#"Columna duplicada",{"Fecha"}),
    #"Columnas con nombre cambiado" = Table.RenameColumns(#"Columnas quitadas",{{"Fecha - Copia", "Fecha"}})
in
    #"Columnas con nombre cambiado"

3.- Definición de Períodos Seleccionables con DAX: Una vez establecidas la tabla de calendario y la fecha de actualización, pasamos a DAX para crear distintos períodos seleccionables como “Hoy”, “Esta semana”, “Este mes”, entre otros. Utilizamos una nueva tabla en la vista de tablas de Power BI y creamos la siguiente estructura con DAX:

Periodos = UNION(
            ADDCOLUMNS(
            CALENDAR(                                 
                MAX('ÚltimaActualización'[UltimaActualización]) - WEEKDAY(MAX('ÚltimaActualización'[UltimaActualización]), 1) + 2,    
                MAX('ÚltimaActualización'[UltimaActualización])  
            ),
            "Tipo", "Semana Hasta la Fecha",
            "Orden", 2
            ),    
            ADDCOLUMNS(
            CALENDAR(
                MAX('ÚltimaActualización'[UltimaActualización]) - WEEKDAY(MAX('ÚltimaActualización'[UltimaActualización]), 1) - 5,
                MAX('ÚltimaActualización'[UltimaActualización]) - WEEKDAY(MAX('ÚltimaActualización'[UltimaActualización]), 1) + 1
            ),
            "Tipo", "Semana Anterior",
            "Orden", 8
            ),
            ADDCOLUMNS(
            CALENDAR(
                date(year(MAX('ÚltimaActualización'[UltimaActualización])),month(MAX('ÚltimaActualización'[UltimaActualización])), 1),
                MAX('ÚltimaActualización'[UltimaActualización])
            ), 
            "Tipo", "Mes Hasta la Fecha",
            "Orden", 3
            ),
            ADDCOLUMNS(
            CALENDAR(
                date(year(MAX('ÚltimaActualización'[UltimaActualización])),month(MAX('ÚltimaActualización'[UltimaActualización]))-1, 1),
                EOMONTH(date(year(MAX('ÚltimaActualización'[UltimaActualización])),month(MAX('ÚltimaActualización'[UltimaActualización]))-1, 1),0)
            ), 
            "Tipo", "Mes Anterior",
            "Orden", 9
            ),
            ADDCOLUMNS(
            CALENDAR(
                date(year(MAX('ÚltimaActualización'[UltimaActualización])),month(MAX('ÚltimaActualización'[UltimaActualización])), 1),
                EOMONTH(MAX('ÚltimaActualización'[UltimaActualización]),0)
            ), 
            "Type", "Mes Actual",
            "Orden", 6
            ),
            ADDCOLUMNS(
            CALENDAR(
                date(year(MAX('ÚltimaActualización'[UltimaActualización])),1, 1),
                MAX('ÚltimaActualización'[UltimaActualización])
            ), 
            "Tipo", "Año Hasta la Fecha",
            "Orden", 4
            ),
            ADDCOLUMNS(CALENDAR(min(Calendario[Fecha]),max(Calendario[Fecha])),
         "Tipo", "Personalizado","Orden",10),
            ADDCOLUMNS(
            CALENDAR(
                date(year(MAX('ÚltimaActualización'[UltimaActualización]))-1,1, 1),
                date(year(MAX('ÚltimaActualización'[UltimaActualización]))-1,12, 31)
            ), 
            "Tipo", "Año Anterior",
            "Orden", 10
            ),
            ADDCOLUMNS(
            CALENDAR(
                date(year(MAX('ÚltimaActualización'[UltimaActualización])),1, 1),
                date(year(MAX('ÚltimaActualización'[UltimaActualización])),12, 31)
            ), 
            "Tipo", "Año Actual",
            "Orden", 7
        ),
        ADDCOLUMNS(
            CALENDAR(                                 
                MAX('ÚltimaActualización'[UltimaActualización]) - WEEKDAY(MAX('ÚltimaActualización'[UltimaActualización]), 1) + 2,    
        MAX('ÚltimaActualización'[UltimaActualización]) + (7 - WEEKDAY(MAX('ÚltimaActualización'[UltimaActualización]), 2))  
            ),
            "Tipo", "Semana Actual",
            "Orden", 5
        ),

        ADDCOLUMNS(
            CALENDAR(                                 
                MAX('ÚltimaActualización'[UltimaActualización]),
                MAX('ÚltimaActualización'[UltimaActualización])
            ),
            "Tipo", "Hoy",
            "Orden", 1
        )
)

4.- Medidas para el Análisis de Períodos: Es crucial almacenar en una medida el período seleccionado por el usuario. Esto nos permite identificar cuándo el período es personalizado y, por ende, filtrar según la selección realizada en el calendario. Las medidas relevantes son:

Periodo Seleccionado = SELECTEDVALUE('Periodos'[Tipo])

 MinDate = if(ISFILTERED(Calendario[Fecha]) && [Periodo  Seleccionado] = "Personalizado", min(Calendario[Fecha]),min(Periodos[Date]))

MaxDate = if(ISFILTERED(Calendario[Fecha]) && [Periodo Seleccionado] = "Personalizado", max(Calendario[Fecha]), MAX(Periodos[Date]))

Periodo Analizado = [MinDate] & "-" & [MaxDate]

6.- Integración de un Slider para Selección de Fechas: Para que el usuario pueda seleccionar un rango de fechas personalizado, incorporamos un slider que permite seleccionar fechas de manera interactiva. Este slider se vincula directamente con la tabla de Calendario que hemos creado previamente. Opcionalmente, se pueden configurar mediante marcadores para mostar u ocultar este calendario.

7.- Implementación de Medidas para Visualizaciones: Con las medidas anteriores, podemos crear medidas que se utilizarán en tablas y gráficos. Es importante especificar siempre el filtro de fecha dentro de la medida, como se muestra en el siguiente ejemplo:

Ventas Año Actual = 
CALCULATE(
    sum(FactTable[Importe]),
FILTER (FactTable, FactTable[fecha] <= [MaxDate] && FactTable[fecha] >= [MinDate]))

Siguiendo estos pasos, podrás implementar un selector de fechas personalizado en Power BI que mejore significativamente la interacción del usuario con los informes. Este enfoque permite una selección de fechas más intuitiva y adaptada a las necesidades específicas del análisis de datos.

Segueix-nos