¡Jueves de post y video! Aprovechando un par de preguntas que me lanzasteis varios de vosotros sobre como calcular la semana bajo la norma ISO:8601 en el Global Power Platform Bootcamp, he querido realizar un post paso a paso para el cálculo de la semana ISO en Power Query y poder añadirlo a nuestra dimensión tiempo de nuestro dataflow.

Lo primero que debemos saber es que nos dice la norma ISO: 8601y es lo siguiente:

 

La norma ISO 8601 establece que una semana inicia el día lunes y termina el día domingo. Además, esta norma indica que se considera la primera semana de un año aquella que contiene el primer jueves de dicho año, o lo que es lo mismo, aquella que contiene el día 4 de enero.

 

¿Y esto que quiere decir? Bien, que el primer día de la semana es el lunes (1) y el último día es el domingo (7). Las semanas tienen 7 días y y su numeración siempre empiezan en 1. Además, la primera semana del año, la semana 1, es aquella contiene el primer jueves de dicho año, o lo que es lo mismo, el 4 de enero.

Con estos criterios, podemos calcular la semana ISO, ¿no creéis? Vamos a ello.

Lo primero que tenemos que hacer es calcular la semana del año, ¿y cómo calculamos la semana de un año? Dividiendo el número de días del año (365 o 366) entre 7 que son los días de la semana (de lunes a domingo). En función de si el año es bisiesto o no, nos va devolver un valor de 52,14 (365/7) o 52,28 (366/7).

Pero esto no es lo que nosotros necesitamos saber, nosotros lo que necesitamos saber es a qué semana corresponde la columna fecha de nuestra dimensión calendario:

 
 
 
 

¿Y cómo lo hacemos? De la siguiente manera:

Lo primero que tenemos que hacer es calcular es el número de día del año que corresponde la fecha en cuestión, es decir, del 1 al 365 o 366 en función de si es bisiesto o no. Para ello usamos la función Date.DayOfYear:

DiaAño = Date.DayOfYear([Fecha])

 
 
 

Una vez obtenido en número del día, calculamos el número del día dentro de la semana. Acordaros que la norma ISO indica que el primer día de la semana es Lunes y el último el Domingo, por ello el lunes tiene que tener el valor 1 y el domingo el valor 7. Para ello usamos la función Date.DayOfWeek y le indicamos que el primer día de la semana es el lunes y le sumamos 1 para su valor sea es, y nos queda:

 

DiaSemana = (Date.DayOfWeek([Fecha], Day.Monday)+1)

  

 
 
 

A continuación, restamos al número día que el corresponde de la fecha en cuestión, el número de la semana. Y le sumamos 10, así evitamos por ejemplo los posibles negativos y el resultado obtenido lo dividimos entre 7 que es el número de días de una semana. Y de esta manera ya tenemos nuestra semana calculada:

Semana = ([DiaAño]- [DiaSemana]+10)/7

 

 

Pero como las semanas en mi pueblo son números enteros, lo que debemos hacer es redondear a la baja para obtener el número de la semana. Para ello:

Semana = Number.RoundDown= ([DiaAño]- [DiaSemana]+10)/7)

 

Y de esta manera ya tenemos nuestras semanas ¿no? Pero…

 

 
 
 
 
 

Aquí podemos obtener un resultado igual a 0 o a 53 entre otros, ¿qué significa que obtengamos un 0 o un 53? ¿Qué hemos hecho mal el cálculo? No, significa que la fecha en cuestión pertenece al año anterior o el año en cuestión tiene 53 semanas.

Empecemos por la semana 0. Al ser 0, pertenece al año anterior y puede ser semana 52 o semana 53. Para calcularlo lo que debemos obtener el número de días del año anterior, y restarle el día de la semana a la que pertenece ese día, es decir, el último día del año anterior, agregarle 10 y dividirlo entre el número de días de una semana, o lo que es lo mismo entre 7. Exactamente lo mismo que para el cálculo de la semana, pero para el año anterior.

Para ello obtenemos fecha final del año anterior, es por eso que le restamos 1 al año obtenido de nuestra columna Fecha. Y con la función #date, lo que hacemos es crear un valor de fecha a partir de números enteros que representan el año (obtenido con la función Date.Year), mes y día los introducimos manualmente:

FinalAñoAnteriorFecha=#date(Date.Year([Fecha])-1,12,31))

 
 

Calculamos el número de días que tiene el año anterior, a nuestra columna Fecha, o lo que es lo mismo, el número de días que tiene el año de la columna FinalAñoAnteriorFecha:

 

NumeroDiasAñoAnterior= Date.DayOfYear(FinalAñoAnteriorFecha)


 
 
 

Ahora tenemos que obtener el día de la semana de la fecha fin del año anterior, es decir, de la columna FinalAñoAnteriorFecha. Cómo queremos que empiece el lunes en 1, le sumamos 1 como hasta ahora.:

DiaSemanaFinalAñoAnterior = (Date.DayOfWeek([FinAñoAnteriorFecha], Day.Monday)+1)

 
 
 

A continuación, como hemos hecho con la semana del año actual, le sumamos 10 y dividimos entre 7. Y lo de siempre, los días tienen que ser números enteros, por lo que redondeamos hacia abajo.

SemanaAñoAnterior = Number.RoundDown((([NumeroDiasAñoAnterior]-[DiaSemanaFinalAñoAnterior])+10)/7)

 
 
 

Y como podemos ver, hay fechas que pueden pertenecer a la semana 52 o semana 53 del año anterior.

Hasta aquí hemos cubierto todas las semana exceptuando la 53, y la semana 0 que puede ser la semana 52 o 53. ¿Bien?

La otra posibildiad es que la semana calculada sea igual a 53, entonces debemos comprobar que realmente si pertenece o no a la semana 1 del año. ¿Cómo sabemos si pertenece al año actual? Como indica la norma ISO:8601 si la semana es igual a 53 y el día de la semana de la fecha fin del año es menor a 4 (jueves) entonces es semana 1, sino es semana 53. Pues vamos a ello. Calculamos lo primero la fecha fin del año, para ello:

 

Ahora lo que debemos de obtener es el día de la semana del final del año para ver si es inferior a 4. De ser así, según la norma ISO es semana 1, sino, será semana 53. Por lo que creamos otra columna nueva pero esta vez al introducir la comparativa, le indicamos que la columna es de tipo True/False:

 
 
 

DiaSemanaFinAñoFecha  = Date.DayOfWeek([FinalAñoFecha], Day.Monday)+1<4

 

Ahora ya, tenemos todas las variantes posibles para crear la columna con la semana ISO. Para ello, creamos una nueva columna personalizada en la que escribimos la siguiente condición:


 
 

if [Semana_]= 0

then [SemanaAñoAnterior]

else if ([Semana_]= 53 and [DiaSemanaFinAñoFecha]=true)

then 1

else

[Semana_]

 
 
 
 

Si obtenemos ahora la columna de semana mediante la interfaz de usuario de Power Query, vamos a ver las diferencias a la hora de numerar las columnas:

 

 
 

Y hasta aquí es todo, lo único que me queda por añadir es que para este ejemplo, he realizado todos los pasos muy despedazados y he creado columnas innecesarias a modo ilustrativo, pero que sepáis que se puede o se debe agrupar todo en un único paso quedando el código M de la siguiente manera:

 

if  Number.RoundDown((Date.DayOfYear([Fecha])-(Date.DayOfWeek([Fecha], Day.Monday)+1)+10)/7)=0 

then 

    Number.RoundDown((Date.DayOfYear(#date(Date.Year([Fecha])-1,12,31))-(Date.DayOfWeek(#date(Date.Year([Fecha])-1,12,31), Day.Monday)+1)+10)/7) 

else if

    (Number.RoundDown((Date.DayOfYear([Fecha])-(Date.DayOfWeek([Fecha], Day.Monday)+1)+10)/7)=53 and (Date.DayOfWeek(#date(Date.Year([Fecha]),12,31), Day.Monday)+1<4)) 

then 1 

else 

   Number.RoundDown((Date.DayOfYear([Fecha])-(Date.DayOfWeek([Fecha], Day.Monday)+1)+10)/7)

 

Y con esto ya tenemos nuestra columna de semana bajo norma ISO:8601. Espero que os guste y os sea útil. Os dejo aquí el video:

 



¡Nos vemos en los datos!