Calcular tabla de fechas a partir de marcas de tiempo

[jeronimo2334]

Hola

Esperaba poder obtener alguna información sobre cómo lograr esto a través de DAX o SQL :

Tengo una tabla con 3 columnas: ids, longitudes y marcas de tiempo. Lo que estoy tratando de hacer es crear una nueva tabla que tenga id, número de semana, fecha. Para calcular la nueva tabla tomamos la marca de tiempo de la primera tabla y agregamos días iguales al número de longitud multiplicado por 7 porque cada longitud es una semana. La columna de la semana comienza en 1 de forma predeterminada y, a continuación, aumenta en función del número de longitud.

Lo sé, es un poco confuso, avísame si debo proporcionar más información.

[Shak85]

Hola a todos, quiero crear una tabla de fechas en el siguiente formato. Si seleccionamos Semanalmente, todas las fechas se muestran con la semana que termina en la fecha excepto la última semana con "semana actual" igual para meses trimestre y año.

Fecha	Período de tiempo	Intervalo de tiempo
01/04/2020	Semanal	Semana actual (05-04-2020)
		P/e 29/03/20220
		con 22/03/2020
		con 15/03/2020
	Mensual	Mes actual (abril)
		Marzo
		Febrero
		Enero
	Trimestral	Trimestre actual (T1 2020)
		Q4 2019
		Tercer trimestre de 2019

Gracias

[edhans]

@Shak85 por favor cree un nuevo hilo para su pregunta. Este subproceso se ha marcado como resuelto y no puede tener dos soluciones a dos problemas diferentes.

[Greg_Deckler]

¿Esa es la tabla de origen o la tabla deseada? Por favor, consulte este post sobre cómo obtener su pregunta respondida rápidamente: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

¡Muestra datos como texto!

[jeronimo2334]

@Greg_Deckler

Tengo esto:

Quiero esto:

Id	Fecha	Semana
32587	2/5/2020	1
32587	2/6/2020	1
32587	2/7/2020	1
32587	2/8/2020	1
32587	2/9/2020	1
32587	2/10/2020	1
32587	2/11/2020	1
32587	2/12/2020	2
32587	2/13/2020	2
32587	2/14/2020	2
32587	2/15/2020	2
32587	2/16/2020	2
32587	2/17/2020	2
32587	2/18/2020	2
32587	2/19/2020	3
32587	2/20/2020	3
32587	2/21/2020	3
32587	2/22/2020	3
32587	2/23/2020	3
32587	2/24/2020	3
32587	2/25/2020	3
93024	5/12/2020	1
93024	5/13/2020	1
93024	5/14/2020	1
93024	5/15/2020	1
93024	5/16/2020	1
93024	5/17/2020	1
93024	5/18/2020	1
95379	2/22/2019	1
95379	2/23/2019	1
95379	2/24/2019	1
95379	2/25/2019	1
95379	2/26/2019	1
95379	2/27/2019	1
95379	2/28/2019	1
95379	3/1/2019	2
95379	3/2/2019	2
95379	3/3/2019	2
95379	3/4/2019	2
95379	3/5/2019	2
95379	3/6/2019	2
95379	3/7/2019	2

Básicamente, cada identificador de la tabla original es un índice para la nueva tabla. Cada marca de tiempo de la tabla original se utiliza para crear la entrada de fecha inicial y, a continuación, tiene que incrementar automáticamente las fechas en función del número de longitudes de la tabla original por 7, ya que la longitud es una semana.

[Greg_Deckler]

Correcto, @jeronimo2334 pegue el código fuente como texto como pegó el resultado que desea. De lo contrario, tengo que escribir todos sus datos para probar y no voy a hacer eso.

[jeronimo2334]

@Greg_Deckler No sabía que estabas haciendo eso, ¡lo aprecié!

Tabla original:

Id	weekLength	Timestamp
84893	14	1575892800
86848	2	1582459200
89665	15	1575288000
89668	9	1584964800
90115	16	1575288000
90262	17	1579435200
90449	27	1573905600
90532	11	1583755200
90556	16	1575288000
90581	16	1575288000
90635	12	1578312000
90701	16	1575288000
90723	18	1575201600
90771	20	1578312000
91571	15	1575288000
91672	15	1575288000
91708	18	1575892800
91724	12	1577966400
91894	18	1574683200

[jeronimo2334]

@Greg_Deckler Antes de entrar demasiado profundo, acabo de recordar que una nueva semana debería aumentar los domingos y no cada 8o día.

[Greg_Deckler]

@jeronimo2334 - OK, así que ahora necesito entender cómo se está recuperando de sus datos de origen a su resultado previsto.

¿Está diciendo que dado un identificador y una marca de tiempo y el número de semanas que desea generar una fila para cada fecha de la marca de tiempo de fecha de inicio para el número de semanas especificado. Así que, si hubiera

ID 1, Semanas 1 y una marca de tiempo que se tradujo a 1/1/2020 conseguiría filas como:

ID, Fecha, Semana

1,1/1/2020,1

1,1/2/2020,1

1,1/3/2020,1

...

1,1/7/2020,1

Por ejemplo.

[jeronimo2334]

@Greg_Deckler ¡Precisamente! La única advertencia es que el indicador de la semana tendría que incrementarcada cada domingo para indicar que es una nueva semana, no cada 7 días.

[Greg_Deckler]

Bien, @jeronimo2334 , ahora estamos llegando a algún lado. Datos de muestra, resultados esperados y una explicación de cómo llegar del punto A al punto B. Déjame echar un vistazo.

[Greg_Deckler]

Muy bien @jeronimo2334 esto tomó un poco de TRABAJO!! Tuve que sacar una bolsa entera de trucos en este caso. Comienza con la conversión de la fecha de la época de Unix a UTC:

firstDate = 
VAR UnixDays = [timeStamp]/(60*60*24)
RETURN (DATEVALUE("1/1/1970")+UnixDays)

Entonces esta monstruosidad. PBIX está unido.

Table 2 = 
    VAR __Table =
        SUMMARIZE(
            'Table',
            'Table'[id],
            'Table'[weekLength],
            'Table'[firstDate],
            "__DateNum1",INT([firstDate])
        )
    VAR __Calendar = 
        ADDCOLUMNS(
            CALENDAR(
                DATE(2019,1,1),
                DATE(2021,12,31)
            ),
            "__Weeknum",WEEKNUM([Date],17),
            "__DateNum2",INT([Date]),
            "__Year",YEAR([Date])
        )
    VAR __GeneratedTable = 
            FILTER(
                GENERATE(__Table,__Calendar),
                [__DateNum2]>=[__DateNum1]
            )
    VAR __GeneratedTable2 = 
        ADDCOLUMNS(
            __GeneratedTable,
            "__Sequential",
                VAR MaxWeeks = SUMMARIZE(FILTER(__GeneratedTable,[id]=EARLIER([id])),[__Year],"MaxWeek",MAXX(FILTER(__GeneratedTable,[id]=EARLIER([id])),[__Weeknum]))
                VAR MyYear = [__Year]
                VAR MyStart = SUMX(FILTER(MaxWeeks,[__Year]<MyYear),[MaxWeek])
                VAR firstYear = MINX(FILTER(__GeneratedTable,[id]=EARLIER([id])),[__Year])
                VAR myNum = IF(MyYear=firstYear,[__Weeknum],MyStart+[__Weeknum])
                RETURN myNum
        )
    VAR __GeneratedTable3 = 
        ADDCOLUMNS(
            __GeneratedTable2,
            "__WeeksFromMin",
                [__Sequential] - MINX(FILTER(__GeneratedTable2,[id]=EARLIER([id])),[__Sequential]) + 1
        )
RETURN
    SELECTCOLUMNS(
        FILTER(
            __GeneratedTable3,
            [__WeeksFromMin] <= [weekLength]
        ),
        "id",[id],
        "date",[Date],
        "week",[__Sequential]
    )

[jeronimo2334]

@Greg_Deckler ¡Esto es increíble! Una cosa, sin embargo, la semana debe ser sólo un contador, no una representación de una semana en el año calendario.

En otras palabras, la columna de la semana no debe ser mayor que la semana duración original en ningún momento. Por ejemplo, si tiene una fila como:

id - weekLength - firstDate

23 - 5 - 1584964800

La primera semana debe comenzar con el contador de la semana 1, luego en el primer domingo incrementamos el contador de la semana a 2 y así sucesivamente hasta que lleguemos a la semana 5. La razón por la que menciono mucho el domingo es porque las marcas de tiempo firstDate podrían ser cualquier día de la semana, por lo que no podemos incrementar el contador mediante programación.

[Greg_Deckler · Accepted solution]

Sí. 🙂 En serio, @jeronimo2334, esto fue un verdadero desafío.

Así que pensé en eso. Creo que solo tienes que hacer una edición muy pequeña:

Table 2 = 
    VAR __Table =
        SUMMARIZE(
            'Table',
            'Table'[id],
            'Table'[weekLength],
            'Table'[firstDate],
            "__DateNum1",INT([firstDate])
        )
    VAR __Calendar = 
        ADDCOLUMNS(
            CALENDAR(
                DATE(2019,1,1),
                DATE(2021,12,31)
            ),
            "__Weeknum",WEEKNUM([Date],17),
            "__DateNum2",INT([Date]),
            "__Year",YEAR([Date])
        )
    VAR __GeneratedTable = 
            FILTER(
                GENERATE(__Table,__Calendar),
                [__DateNum2]>=[__DateNum1]
            )
    VAR __GeneratedTable2 = 
        ADDCOLUMNS(
            __GeneratedTable,
            "__Sequential",
                VAR MaxWeeks = SUMMARIZE(FILTER(__GeneratedTable,[id]=EARLIER([id])),[__Year],"MaxWeek",MAXX(FILTER(__GeneratedTable,[id]=EARLIER([id])),[__Weeknum]))
                VAR MyYear = [__Year]
                VAR MyStart = SUMX(FILTER(MaxWeeks,[__Year]<MyYear),[MaxWeek])
                VAR firstYear = MINX(FILTER(__GeneratedTable,[id]=EARLIER([id])),[__Year])
                VAR myNum = IF(MyYear=firstYear,[__Weeknum],MyStart+[__Weeknum])
                RETURN myNum
        )
    VAR __GeneratedTable3 = 
        ADDCOLUMNS(
            __GeneratedTable2,
            "__WeeksFromMin",
                [__Sequential] - MINX(FILTER(__GeneratedTable2,[id]=EARLIER([id])),[__Sequential]) + 1
        )
RETURN
    SELECTCOLUMNS(
        FILTER(
            __GeneratedTable3,
            [__WeeksFromMin] <= [weekLength]
        ),
        "id",[id],
        "date",[Date],
        "week",[__WeeksFromMin]
    )

[jeronimo2334]

@Greg_Deckler, eres fenomenal! No tenía ninguna expectativa cuando empecé a trabajar en esto, ¡pero ahora puedo entregar mi proyecto! Muchas gracias, señor.

Una última cosa si no es demasiado, ¿le importaría agregar algunos comentarios en su fragmento de código sólo para que pueda tratar de entender cómo todo esto se está uniendo.

[Greg_Deckler]

OK, @jeronimo2334 así que te guiaré a través del código y sólo FYI el código está escrito de tal manera que lo estaba descubriendo en el camino, no está de ninguna manera optimizado ni algo por el estilo.

Así que comencé con la premisa de que iba a tener que usar GENERATE para tomar su tabla de hechos y crear un producto cartesiano contra una tabla de fechas. Cada vez que vea que DAX necesita crear filas de la nada, es una buena apuesta que GENERATE va a estar involucrado. O GENERATESERIES.

Por lo tanto, lo primero que hay que hacer es obtener una represenetation de su mesa. Eso es __Table. Probablemente no necesitaba usar SUMMARIZE aquí, probablemente podría haber utilizado ADDCOLUMNS. Sus horarios eran en 12:00:00 PM, que no coincide con 12:00:00 AM que es la fecha / horas creadas por la función CALENDAR por lo que __DateNum1 utiliza INT para devolver la parte del día del valor de fecha / hora, eliminando el componente de hora.

A continuación, necesitamos nuestra tabla Calendario. He utilizado el principio de mínimos/máximos razonables, es posible que tenga que cambiar el rango aquí. Añadido en __Weeknum columna usando WEEKNUM con 17 que es un truco DAX indocumentado que comienza una nueva semana el domingo. __DateNum2 es la misma historia que __DateNum1. También es necesario agregar __Year porque vamos a necesitar __Sequential más adelante.

Así, en __GeneratedTable, podemos usar GENERATE para crear el producto cartesiano de nuestras dos tablas. A continuación, podemos FILTRAR esto para cualquier fila en la que __DateNum2 > __DateNum1. Si __DateNum2 < __DateNum1 no queremos esas filas porque __DateNum1 es nuestra "firstDate".

Ahora viene un poco de diversión. Necesitamos agregar un identificador de semana secuencial por ID a nuestro __GeneratedTable y llamar a la nueva tabla __GeneratedTable2. Sequential agrega un número de semana secuencial. Ahora, puede haber un problema aquí, ya que esta versión no tiene en cuenta las semanas incompletas al final del año. He creado una versión que explica esto que puede que tenga que desenterrar.

Así que ahora podemos crear __GeneratedTable3. En este __WeeksFromMin encontramos el valor secuencial mínimo para cada id y lo restamos de nuestro valor de semana secuencial y agregamos 1. Ahora tenemos nuestro contador de la semana. Por lo tanto, en nuestro Return, simplemente FILTRAmos nuestro __GeneratedTable3 para filas donde el __WeeksFromMin es menor o igual que el weekLength deseado. Y usamos SELECTCOLUMNS para deshacernos de todas las columnas innecesarias que hemos creado.

[jeronimo2334]

@Greg_Deckler muchas gracias de nuevo!

Acabo de notar un problema con semanas que incrementan incorrectamente cuando el año cambia. Se ha añadido una imagen que muestra cómo la semana se incrementó el miércoles en lugar del domingo.

[Greg_Deckler]

Sí, a eso me refería con respecto a las semanas de fin de año. Es porque WEEKNUM es por año por lo que una semana que abarca años va a tener parte de ella ser 52/53 y la otra parte 1. Tengo una solución para ello, déjame desenterrarlo.

[Greg_Deckler]

Bien, @jeronimo2334 creo que lo tengo, tuve que ajustarlo un poco para que circunstancias particulares. Probablemente necesite crear una medida rápida secuencial 2 que establezca este patrón. Los únicos cambios fueron en la __Sequential la creación de columnas. Básicamente, en la variable de tabla MaxWeek, agrego una columna llamada __Count, que cuenta cuántos días hay en la última semana del año. A continuación, agrego una variable de ajuste que cuenta cuántos años anteriores el __Count es menor que 7. A continuación, simplemente podemos ajustar (restar) que muchos números de semana de nuestro cálculo para __Sequential. Parece que sirve de nada.

Table 2 = 
    VAR __Table =
        SUMMARIZE(
            'Table',
            'Table'[id],
            'Table'[weekLength],
            'Table'[firstDate],
            "__DateNum1",INT([firstDate])
        )
    VAR __Calendar = 
        ADDCOLUMNS(
            CALENDAR(
                DATE(2019,1,1),
                DATE(2021,12,31)
            ),
            "__Weeknum",WEEKNUM([Date],17),
            "__DateNum2",INT([Date]),
            "__Year",YEAR([Date])
        )
    VAR __GeneratedTable = 
            FILTER(
                GENERATE(__Table,__Calendar),
                [__DateNum2]>=[__DateNum1]
            )
    VAR __GeneratedTable2 = 
        ADDCOLUMNS(
            __GeneratedTable,
            "__Sequential",
                VAR MaxWeeks = ADDCOLUMNS(SUMMARIZE(FILTER(__GeneratedTable,[id]=EARLIER([id])),[__Year],"MaxWeek",MAXX(FILTER(__GeneratedTable,[id]=EARLIER([id])),[__Weeknum])),"__Count",COUNTROWS(FILTER(__GeneratedTable,[id]=EARLIER([id])&&[__Year]=EARLIER([__Year]) && [__Weeknum]=[MaxWeek])))
                VAR MyYear = [__Year]
                VAR MyStart = SUMX(FILTER(MaxWeeks,[__Year]<MyYear),[MaxWeek])
                VAR firstYear = MINX(FILTER(__GeneratedTable,[id]=EARLIER([id])),[__Year])
                VAR adjusment = COUNTROWS(FILTER(MaxWeeks,[__Year]<MyYear && [__Count] < 7))
                VAR myNum = IF(MyYear=firstYear,[__Weeknum],MyStart+[__Weeknum]-adjusment)
                RETURN myNum
        )
    VAR __GeneratedTable3 = 
        ADDCOLUMNS(
            __GeneratedTable2,
            "__WeeksFromMin",
                [__Sequential] - MINX(FILTER(__GeneratedTable2,[id]=EARLIER([id])),[__Sequential]) + 1
        )
RETURN
    SELECTCOLUMNS(
        FILTER(
            __GeneratedTable3,
            [__WeeksFromMin] <= [weekLength]
        ),
        "id",[id],
        "date",[Date],
        "week",[__WeeksFromMin]
    )

[jeronimo2334]

@Greg_Deckler, Impresionante, que hizo el truco!

[amitchandak]

@jeronimo2334 , debe ser en segundo o milisegundo. Puede crear nuevas columnas como

Fecha: dateadd(date(1900,1,1),[firstdate]/(24*60*60),DAY)

WeekNum á weeknum ([Fecha])

Día de la semana: día de la semana([fecha])