ISO week number and year from number of weeks required.

[Anonymous]

I am trying to extract data from a Data Cube, the Cube parameter required is Start Week and End Week in ISO Week number and Year.  

e.g. If I need past 25 weeks of data, the parameter value will be

Start Week = 2020W53 ( 24 weeks ago) 

End Week = 2021W24 (as of today 16Jun21)

It is relatively easier in DAX but I am struggling in Power Query, how do I generate the string 2020W53 and 2021W24 based on input (25) and today's date?

[watkinnc · Accepted solution]

Although not at a PC, here is your process:

let

Today = Date.From(DateTime.LocalNow()),

DateMinus25Weeks = Date.AddWeeks(Today, -25),

TodayWeek = Date.WeekOfYear(Today),

WeekMinus25 = Date.WeekOfYear(DateMinus25Weeks),

CurrentYear = Year.From(Today),

YearMinus25 = Year.From(DateMinus25Weeks),

//At this point, please check to make sure //that your columns are text, or else this //next step fails. If you need to change your columns to text, do that now.

FromRange = YearMinus25&"W"&WeekMinus25,

ToRange = CurrentYear&"W"&TodayWeek

in

{[Start Week = ToRange, End Week = FromRange]}

--Nate

[Anonymous]

Please look Function for ISO Year/Week number (ISO 8601)) 

NB! Pay attention to offsetindays parameter (by default it equals to zero - it means week starts on Sunday)

[Anonymous]

Thank you for the clue!

[v-jingzhang · Accepted solution]

Hi @Anonymous 

You can create an M function by using codes from this link to convert a given date to ISO Week. Modify the output format per your need. I name this function as DateToISOWeek.

/*
based on <https://en.wikipedia.org/wiki/ISO_week_date#Calculating_the_week_number_of_a_given_date>

M / Power Query doesn't have a native ISO8601 Week Number function, and DAX's
`weeknum(x, 21)` doesn't give the correct ISO Week-Year.

homepage: <https://gist.github.com/r-k-b/18d898e5eed786c9240e3804b167a5ca>
*/

let
    getISO8601Week = (someDate as date) =>
        let
            getDayOfWeek = (d as date) =>
                let
                    result = 1 + Date.DayOfWeek(d, Day.Monday)
                in
                    result,

            getNaiveWeek = (inDate as date) =>
                let
                    // monday = 1, sunday = 7
                    weekday = getDayOfWeek(inDate),

                    weekdayOfJan4th = getDayOfWeek(#date(Date.Year(inDate), 1, 4)),

                    ordinal = Date.DayOfYear(inDate),

                    naiveWeek = Number.RoundDown(
                        (ordinal - weekday + 10) / 7
                    )
                in
                    naiveWeek,

            thisYear = Date.Year(someDate),

            priorYear = thisYear - 1,

            nwn = getNaiveWeek(someDate),

            lastWeekOfPriorYear =
                getNaiveWeek(#date(priorYear, 12, 28)),

            // http://stackoverflow.com/a/34092382/2014893
            lastWeekOfThisYear =
                getNaiveWeek(#date(thisYear, 12, 28)),

            weekYear =
                if
                    nwn < 1
                then
                    priorYear
                else
                    if
                        nwn > lastWeekOfThisYear
                    then
                        thisYear + 1
                    else
                        thisYear,

            weekNumber =
                if
                    nwn < 1
                then
                    lastWeekOfPriorYear
                else
                    if
                        nwn > lastWeekOfThisYear
                    then
                        1
                    else
                        nwn,

            week_dateString =
                Text.PadStart(
                    Text.From(
                        Number.RoundDown(weekNumber)
                    ),
                    2,
                    "0"
                )
        in
            /*
            Text.From(weekYear) & "-W" & week_dateString & "-" & Text.From(getDayOfWeek(someDate))
            */
            Text.From(weekYear) & "W" & week_dateString
in
    getISO8601Week

Then you can invoke this function in other queries to get the Start Week and End Week. For example,

let
    Today = Date.From(DateTime.LocalNow()),
    NumberOfWeeks = 25,
    EndWeek = DateToISOWeek(Today),
    StartWeek = DateToISOWeek(Date.AddWeeks(Today, 1-NumberOfWeeks))
in
    Table.FromRecords({[FromWeek = StartWeek, ToWeek = EndWeek]})

You can use query parameters to pass values to variables Today and NumberOfWeeks in above query.

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[watkinnc · Accepted solution]

Although not at a PC, here is your process:

let

Today = Date.From(DateTime.LocalNow()),

DateMinus25Weeks = Date.AddWeeks(Today, -25),

TodayWeek = Date.WeekOfYear(Today),

WeekMinus25 = Date.WeekOfYear(DateMinus25Weeks),

CurrentYear = Year.From(Today),

YearMinus25 = Year.From(DateMinus25Weeks),

//At this point, please check to make sure //that your columns are text, or else this //next step fails. If you need to change your columns to text, do that now.

FromRange = YearMinus25&"W"&WeekMinus25,

ToRange = CurrentYear&"W"&TodayWeek

in

{[Start Week = ToRange, End Week = FromRange]}

--Nate

[Anonymous]

According to the ISO week 1 should be the first week with 4 days in the new year in the week. e.g. If 1 Jan is a friday, saturday or sunday they should not be week 1. 

If 1. Jan is on one of these three days than 4. January would be in week 2 if you  count the week numbers from the first day of the year.  

You can make a simple if statement checking 

if 4. Jan = week 1? then use the regular weeknum function else use the regular weeknum calculation minus 1. 

That's it.