How to add Epi Week aka MMWR Week column in Power BI

Question

My dataset has a date column. I need to assign the correct Epi Week/MMWR Week to each row. I can't do it by hand because the dataset spans five years and has over 18,000 rows.

The Power Query function Date.WeekOfYear does not assign the correct week numbers for MMWR Weeks.

MMWR Week guidelines are as follows:

The first day of any MMWR week is Sunday. MMWR week numbering is sequential beginning with 1 and incrementing with each week to a maximum of 52 or 53. MMWR week #1 of an MMWR year is the first week of the year that has at least four days in the calendar year. For example, if January 1 occurs on a Sunday, Monday, Tuesday or Wednesday, the calendar week that includes January 1 would be MMWR week #1. If January 1 occurs on a Thursday, Friday, or Saturday, the calendar week that includes January 1 would be the last MMWR week of the previous year (#52 or #53). Because of this rule, December 29, 30, and 31 could potentially fall into MMWR week #1 of the following MMWR year.

Answer 1

Checking WEEKNUM(date;1) against MMWR (ref below), it seems to give a result off by one (i.e. one higher than expected);
the 1 specifies that weeks begin on Sundays.

That might be the closest you get; possibly adjust by using WEEKNUM(date;1)-1
(NOTE: which might be 0 for the first few days of a year)

Ref:
https://help.libreoffice.org/7.3/en-US/text/scalc/01/func_weeknum.html?&DbPAR=SHARED&System=UNIX

Might one hope that some day, everybody uses ISO 8601 formats. sigh

Ref MMWR:
https://ibis.health.state.nm.us/resource/MMWRWeekCalendar.html

Answer 2

I solved it by first creating an epiweek calendar table in R, then imported the table to my Power BI report and created a relationship between the date column in my epiweek table and the date column in my dataset.

Here's the R code:

library(lubridate)
library(tidyverse)
library(writexl)
daterange <- seq(ymd("2018-01-01"),ymd("2024-12-31"), by = "days")
epiweek_table <- as_tibble(daterange) %>% 
  rename(date = value) %>% 
  mutate(epiweek = epiweek(date))
write_xlsx(epiweek_table, "epiweek_table.xlsx", col_names = TRUE)