Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Function for ISO Year and/or Week number (ISO 8601) with optional offset and output format:
fxISOYearWeek(Date, offsetindays, outFormat)
// Parameters:
// Date - Mandatory Date parameter in date format
// offsetindays (optional) - by default week starts on Sunday (offsetindays=0)
// if offsetindays=1 then week starts on Monday
// outFormat (optional) - by default outFormat="W" and works as year week concatenation string,
// if outFormat is text, it works as concatenation string between Year and Week
// if outFormat=-1, function returns ISO Year as number
// if outFormat= 1, function returns ISO Week as number
Call examples:
// Basic use example:
fxISOYearWeek(#date(2023, 1, 1) ) // Return 2023W01 (by default, week starts on Sunday)
// offsetindays parameter use example:
fxISOYearWeek(#date(2023, 1, 1) , 1) // Return 2022W52 (set start of week on Monday, offsetindays=1)
// outFormat parameter use example:
fxISOYearWeek(#date(2023, 1, 1) , 1,"-Wk") // Return 2022-Wk52 (customize concatenation string)
fxISOYearWeek(#date(2023, 1, 1),null, -1 ) // Return 2023 (ISO Year only: outFormat=-1)
fxISOYearWeek(#date(2023, 1, 1),null, 1 ) // Return 1 (ISO Week only: outFormat=+1)
Function code:
= ( Date as nullable date,
optional offsetindays as nullable number, // by default week starts on Sunday (offsetindays=0)
optional outFormat as nullable any /* by default outFormat="W" and works as year week concatenation string,
if outFormat=-1, returns ISOYear as number
if outFormat= 1, returns ISOWeek as number
*/
)=>
let
outFormat = outFormat??"W",
offsetindays = offsetindays??0, // if first day of week is Monday set Offset=1
Date = Date.AddDays(Date, -offsetindays),
CurrentThursday = Date.AddDays( Date, 3 - Date.DayOfWeek( Date, Day.Sunday )),
ISOYear = Date.Year( CurrentThursday ),
FirstThursdayOfYear =
Date.AddDays( #date(ISOYear, 1, 7) ,
-Date.DayOfWeek( #date( ISOYear, 1, 1 ), Day.Friday )
),
ISO_Week = (Duration.Days( CurrentThursday - FirstThursdayOfYear) +1)/ 7 + 1
in
if Date = null then null
else if outFormat = -1 then ISOYear
else if outFormat = 1 then ISO_Week
else if Value.Type(outFormat) = type text then Number.ToText(ISOYear)&outFormat&Text.PadStart(Number.ToText(ISO_Week),2,"0")
else "Error"
Example to play with:
let
Today =( DateTime.LocalNow()),
#"List of Dates" = List.Dates( Date.FromText("2022-12-28"), 15, #duration( 1, 0, 0, 0 ) ),
#"Converted to Table" = Table.FromList( #"List of Dates", Splitter.SplitByNothing(), type table[Date = Date.Type] ),
#"Insert Day Name Short" = Table.AddColumn( #"Converted to Table", "Day Name Short", each Date.ToText( [Date], "ddd", "EN-us" ), type text),
#"Added ISO Year" = Table.AddColumn(#"Insert Day Name Short", "ISO Year", each fxISOYearWeek([Date],0,-1), Int64.Type),
#"Added ISO Week" = Table.AddColumn(#"Added ISO Year", "ISO Week", each fxISOYearWeek([Date],null, 1), Int64.Type),
#"Added ISO Week (start on Sunday)" = Table.AddColumn(#"Added ISO Week", "ISO YearWeek", each fxISOYearWeek([Date]),Int64.Type),
#"Added ISO Week (start on Monday)" = Table.AddColumn(#"Added ISO Week (start on Sunday)", "ISO YearWeek (start on Monday)", each fxISOYearWeek([Date],1), Int64.Type)
in
#"Added ISO Week (start on Monday)"
Screenshot of the output:
P.S. This post is based on code published by @RickdeGroot in article Create ISO Week and ISO Year in Power Query M (ISO 8601).
Download
Hi @Anonymous ,
Thanks for your sharing!
Best Regards,
Stephen Tao
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
65 | |
61 | |
21 | |
18 | |
12 |