QlikToDax

Week(OrderDate)

Week, WeekYear and WeekDay in Power BI

Qlik's Week() follows the FirstWeekDay, BrokenWeeks and ReferenceDay settings. The defaults come from the app's locale. Apps created with European settings (FirstWeekDay 0, BrokenWeeks 0, ReferenceDay 4) get ISO weeks, where week 1 contains 4 January, and DAX matches that with WEEKNUM(date, 21). Apps with North American settings (weeks start on Sunday, broken weeks, week 1 contains 1 January) match WEEKNUM(date, 1). Check the SET statements at the top of the script. Power Query's Date.WeekOfYear is not ISO, so compute ISO weeks yourself or in DAX. WeekDay returns a dual (0–6 plus the day name). In Power BI, that's a number column plus a name column sorted by it.

Partial / different approach DAX Power Query

Short answer: First check FirstWeekDay, BrokenWeeks and ReferenceDay in the Qlik script. For ISO weeks (European defaults), use WEEKNUM('Date'[Date], 21) and, for a Monday-based 0–6 weekday, WEEKDAY('Date'[Date], 3). For US defaults, use WEEKNUM('Date'[Date], 1) and WEEKDAY('Date'[Date], 1) - 1. Put Day Name and Day Number in the calendar and set Sort by column.

What it does in Qlik

  • Week(date [, first_week_day [, broken_weeks [, reference_day]]]) returns the week number.
  • WeekYear() returns the year the week belongs to, which can differ from Year() around New Year.
  • WeekDay(date [, first_week_day]) returns a dual: the day name, with the number 0–6 relative to the first week day.

Qlik example

Qlik script (calendar)
Week(Date)              AS Week,
WeekYear(Date)          AS WeekYear,
WeekYear(Date) & '-W' & Num(Week(Date), '00') AS YearWeek,
WeekDay(Date)           AS WeekDay

Power BI equivalent

DAX (calendar columns)

DAX
ISO Week = WEEKNUM ( 'Date'[Date], 21 )
ISO Year = YEAR ( 'Date'[Date] + 4 - WEEKDAY ( 'Date'[Date], 2 ) )   // year of that week's Thursday
Year Week = 'Date'[ISO Year] & "-W" & FORMAT ( 'Date'[ISO Week], "00" )
Weekday No = WEEKDAY ( 'Date'[Date], 3 )        // Monday = 0 ... Sunday = 6
Weekday = FORMAT ( 'Date'[Date], "ddd" )         // sort by Weekday No

Power Query (ISO week)

Power Query M
= Table.AddColumn(Cal, "ISO Week", each
    let thu = Date.AddDays([Date], 3 - Date.DayOfWeek([Date], Day.Monday))
    in Number.RoundDown((Date.DayOfYear(thu) - 1) / 7) + 1, Int64.Type)
= Table.AddColumn(Cal, "Weekday No", each Date.DayOfWeek([Date], Day.Monday), Int64.Type)

Gotchas and behaviour differences

  • WEEKNUM(date) with no second argument starts weeks on Sunday and counts the week containing 1 January as week 1. That's the US convention, not ISO. It's the right match only if the Qlik app uses North American week settings. Otherwise pass 21 explicitly.
  • Date.WeekOfYear in Power Query also uses "week containing 1 January = week 1". It doesn't match ISO around New Year, for example on 1 January 2027, which is ISO week 53 of 2026.
  • Never build "Year-Week" from the calendar year. Use the ISO year, or the last days of December land in week 1 of the wrong year.

Official documentation

Related pages

LOAD ... AUTOGENERATE ... WHILE ... IterNo() Master calendar (date table) The Qlik master calendar script maps to a date table built in Power Query (List.Dates) or DAX (CALENDAR), marked as a date table and related to the fact date. It is essential for every time-intelligence function. Direct equivalent MonthStart(date [, period_no]) MonthStart, MonthEnd and period boundaries MonthStart and MonthEnd return the first or last millisecond of a month. Use Date.StartOfMonth and Date.EndOfMonth in Power Query, or EOMONTH in DAX. Note that STARTOFMONTH in DAX is a table function, not the same thing. Direct equivalent Dual('Jan', 1) Dual values Qlik fields can hold a dual value: a text and a number at the same time. Power BI columns have one data type, so you use a text column sorted by a number column (Sort by column). Partial / different approach

Spotted an error or a better pattern? QlikToDax is maintained by a working BI consultant; corrections are welcome via the About page. Last reviewed 2026-10-25.