Als je buiten de VS werkt, is de kans groot dat je organisatie gebruikmaakt van een kalender op basis van het ISO 8601-weeknummeringssysteem. In de ISO-standaard beginnen weken op een maandag en zijn ze altijd zeven dagen lang. Wanneer je met Power BI werkt, kun je ISO-weeknummers eenvoudig ophalen met de DAX-functie WEEKNUM(), door gebruik […]
Als je buiten de VS werkt, is de kans groot dat je organisatie gebruikmaakt van een kalender op basis van het ISO 8601-weeknummeringssysteem. In de ISO-standaard beginnen weken op een maandag en zijn ze altijd zeven dagen lang.
Wanneer je met Power BI werkt, kun je ISO-weeknummers eenvoudig ophalen met de DAX-functie WEEKNUM(), door gebruik te maken van de speciale return-type 21 (wat overigens niet wordt gesuggereerd door de DAX-editor):
ISO Week = WEEKNUM([Date], 21)
Dit is geweldig als je een kalendertabel in DAX wilt maken. De zaken worden echter ingewikkelder als je een kalendertabel in Power Query wilt genereren: de ingebouwde functie Date.WeekOfYear retourneert helemaal geen ISO-weeknummers.
Veel mensen hebben scripts gepubliceerd om ISO-weeknummers te bepalen, maar deze zijn vaak behoorlijk ingewikkeld. In deze blogpost laat ik zien dat je dit slechts twee regels code nodig hebt om dit te doen. Super eenvoudig!
Laten we beginnen met het herzien van de definitie van het ISO-weeknummersysteem. Zoals eerder vermeld, begint elke week op maandag. De kern van de definitie is gebaseerd op één van de twee regels om de eerste week van het jaar te bepalen:
Er zijn een aantal consequenties aan deze definitie verbonden. Een van de belangrijkste is dat 1 januari niet altijd in hetzelfde kalenderjaar valt als de 4e, wat betekent dat we een specifiek ISO-jaar moeten gebruiken. Een ISO-jaar kan 52 of 53 weken hebben; soms vallen de eerste dagen van januari in het voorgaande ISO-jaar (ten opzichte van het kalenderjaar). En soms vallen de laatste dagen van december in het volgende ISO-jaar.
Andere nuttige waarnemingen zijn dat donderdag het midden van de week is en dat op donderdag het ISO-jaar altijd gelijk is aan het kalenderjaar. Dit geeft ons een eenvoudige manier om het ISO-jaar voor elke datum te bepalen: ga gewoon naar de dichtstbijzijnde donderdag en haal het jaar op. Hier is de M-code om de dichtstbijzijnde donderdag te vinden:
ClosestThursday = Date.AddDays(DateInput, -1 * Date.DayOfWeek(DateInput, Day.Monday) + 3)
Hier is DateInput de datum waarvoor we het ISO-jaar willen vinden. De functie Date.DayOfWeek retourneert het dagnummer van de week, beginnend met 0 op maandag (vandaar het Day.Monday-argument). We nemen dus de datum, trekken het dagnummer af (dit brengt ons op de maandag voorafgaand aan onze date) en voegen drie dagen toe om te eindigen op de donderdag.
De volledige M-code voor een aangepaste functie om het ISO-jaar te bepalen vindt u hieronder:
(DateInput as date) as number =>
let
ClosestThursday = Date.AddDays(DateInput, -1 * Date.DayOfWeek(DateInput, Day.Monday) + 3),
Year = Date.Year(ClosestThursday)
in
Year
Laten we nu verder gaan met de werkelijke ISO-weeknummers. De gebruikelijke aanpak is om op de een of andere manier te bepalen hoe ver een datum verwijderd is van 4 januari of de eerste donderdag van het jaar. Het probleem komt tegen het einde van het jaar, wanneer deze aanpak lastig wordt; data kunnen immers in het volgende (ISO) jaar vallen en de afstand tot het begin van het jaar is misschien helemaal niet relevant.
Het wordt echter heel gemakkelijk als je bedenkt dat, aangezien donderdag het midden van de week is, elke datum hetzelfde ISO-weeknummer heeft als de donderdag die er het dichtst bij ligt. We kunnen daarom dezelfde ‘dichtstbijzijnde donderdag’-benadering gebruiken als hierboven, waardoor het eindejaarsbedrog in één keer wordt opgelost. De vraag wordt dan: hoe ver van de eerste donderdag is de dichtstbijzijnde donderdag?
De vraag wordt nog eenvoudiger als we vragen: welke dag van het jaar is de dichtstbijzijnde donderdag? Waarom? Omdat we hiervoor een standaard M-functie kunnen gebruiken: de Day.DayOfYear-functie. Dit is de denklijn:
In short, the M code for the above looks like this:
Number.RoundUp(Date.DayOfYear(ClosestThursday) / 7)
Als aangepaste M-functie:
(DateInput as date) as number =>
let
ClosestThursday = Date.AddDays(DateInput, -1 * Date.DayOfWeek(DateInput, Day.Monday) + 3),
Weeks = Number.RoundUp(Date.DayOfYear(ClosestThursday) / 7)
in
Weeks
Dat is het!
De onderstaande afbeelding toont de resultaten van deze functies en enkele tussenresultaten voor begin 2023. De kolom ‘Week van het jaar’ bevat de standaard Power Query-weeknummerresultaten, die uiteraard heel verschillend zijn.
