Semana ISO en Power Query bajo norma ISO:8601

¡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))