Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register 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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
9 | |
7 | |
6 |