LOAD ... AUTOGENERATE ... WHILE ... IterNo()

Master calendar (date table) in Power BI

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 DAX Power Query Modeling

Short answer: Build one date table (in Power Query, in DAX with CALENDAR, or upstream in the warehouse). Mark it as a date table, relate it to each fact date column, and turn off Auto date/time.

What it does in Qlik

Qlik apps usually generate a calendar in the script. They find the min and max date, AUTOGENERATE one row per day with IterNo(), and derive Year, Month, Quarter and Week. The calendar links to the fact table by sharing the date field's name.

Qlik example

Qlik script

TempRange: LOAD Min(OrderDate) AS MinDate, Max(OrderDate) AS MaxDate RESIDENT Orders;
LET vMin = Num(Peek('MinDate', 0, 'TempRange'));
LET vMax = Num(Peek('MaxDate', 0, 'TempRange'));
DROP TABLE TempRange;

MasterCalendar:
LOAD TempDate AS OrderDate,
     Year(TempDate) AS Year, Month(TempDate) AS Month,
     'Q' & Ceil(Month(TempDate) / 3) AS Quarter, Week(TempDate) AS Week;
LOAD Date($(vMin) + IterNo() - 1) AS TempDate
AUTOGENERATE 1 WHILE $(vMin) + IterNo() - 1 <= $(vMax);

Power BI equivalent

DAX calculated table

DAX

Date =
ADDCOLUMNS (
    CALENDAR (
        DATE ( YEAR ( MIN ( Orders[OrderDate] ) ), 1, 1 ),
        DATE ( YEAR ( MAX ( Orders[OrderDate] ) ), 12, 31 )
    ),
    "Year", YEAR ( [Date] ),
    "Month Number", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "Year Month", YEAR ( [Date] ) * 100 + MONTH ( [Date] ),
    "Quarter", "Q" & QUARTER ( [Date] ),
    "ISO Week", WEEKNUM ( [Date], 21 )
)

Power Query

Power Query M

let
    StartDate = #date(Date.Year(List.Min(Orders[OrderDate])), 1, 1),
    EndDate   = #date(Date.Year(List.Max(Orders[OrderDate])), 12, 31),
    Dates     = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1, 0, 0, 0)),
    AsTable   = Table.FromList(Dates, Splitter.SplitByNothing(), type table [Date = date]),
    AddYear   = Table.AddColumn(AsTable, "Year", each Date.Year([Date]), Int64.Type),
    AddMonth  = Table.AddColumn(AddYear, "Month Number", each Date.Month([Date]), Int64.Type),
    AddName   = Table.AddColumn(AddMonth, "Month", each Date.ToText([Date], [Format = "MMM", Culture = "en-US"]), type text)
in
    AddName

Then: Mark as date table on Date, set Sort by column for Month (by Month Number), and create a relationship from Orders[OrderDate] to 'Date'[Date].

Gotchas and behaviour differences

Power BI does not link by field name. Each fact date needs an explicit relationship, and only one per pair of tables can be active (see synthetic keys for USERELATIONSHIP).
The date table must cover whole years with no gaps for time intelligence. A relationship on a datetime column won't match the date table, so remove the time part in Power Query first.
Qlik's Week() depends on the FirstWeekDay, BrokenWeeks and ReferenceDay variables set at the top of the script. Decide which rule you want. WEEKNUM(d, 21) is ISO 8601.
Turn off File > Options > Current file > Data load > Auto date/time. Otherwise Power BI creates a hidden date table for every date column.
CALENDARAUTO() scans every date column in the model, including birth dates. It can produce a much larger range than expected.

Official documentation

Load (AUTOGENERATE and WHILE clauses) (Qlik Help)
IterNo - script function (Qlik Help)
Create date tables in Power BI Desktop (Microsoft Learn)
CALENDAR function (DAX) (Microsoft Learn)
List.Dates (Power Query M) (Microsoft Learn)

YearToDate(date [, yearoffset [, firstmonth [, todaydate]]]) YearToDate flags and YTD Qlik's YearToDate() flag is relative to the reload date. DAX TOTALYTD is relative to the dates in the filter context. Pick the one that matches what users expect, or rebuild the flag as a calculated column. Partial / different approach 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 Above(Sum(Sales)) Above, Below, Before and After Above() reads the value from the previous row of the chart. Power BI offers three replacements: the OFFSET window function, the PREVIOUS visual calculation, and calendar-aware time intelligence. Each behaves differently when periods are missing. Partial / different approach Peek(field_name [, row_no [, table_name]]) Peek (running balances in the script) Peek reads a field from a row already loaded into the output table. It is used for running balances and for reading values into variables. Power Query has no row-by-row state, so use grouped list logic, a SQL window function upstream, or a DAX measure. 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.