QlikToDax

MonthStart(date [, period_no])

MonthStart, MonthEnd and period boundaries in Power BI

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

Short answer: In Power Query: Date.StartOfMonth([Date]) and Date.EndOfMonth([Date]). In DAX: EOMONTH(d, -1) + 1 for the first day and EOMONTH(d, 0) for the last. Avoid DAX STARTOFMONTH and ENDOFMONTH for this, because they return tables of dates in the filter context.

What it does in Qlik

MonthStart(date [, period_no]) returns the timestamp of the first millisecond of the month that contains date. MonthEnd returns the last millisecond, 23:59:59.999 on the last day. period_no shifts by whole months (-1 is the previous month). The same family exists for weeks, quarters and years (QuarterStart, YearEnd...).

Qlik example

Qlik script
LOAD OrderDate,
     Date(MonthStart(OrderDate)) AS OrderMonth,
     Date(MonthEnd(OrderDate))   AS OrderMonthEnd,
     Date(MonthStart(OrderDate, -1)) AS PreviousMonthStart
FROM [lib://Data/orders.qvd] (qvd);

Power BI equivalent

Power Query columns (preferred for keys and grouping)

Power Query M
= Table.AddColumn(Source, "OrderMonth", each Date.StartOfMonth([OrderDate]), type date)
= Table.AddColumn(Previous, "OrderMonthEnd", each Date.EndOfMonth([OrderDate]), type date)
= Table.AddColumn(Previous2, "PreviousMonthStart", each Date.StartOfMonth(Date.AddMonths([OrderDate], -1)), type date)

DAX (calculated column or inside a measure)

DAX
Order month = EOMONTH ( Orders[OrderDate], -1 ) + 1
Order month end = EOMONTH ( Orders[OrderDate], 0 )
Quarter start = DATE ( YEAR ( Orders[OrderDate] ), ( QUARTER ( Orders[OrderDate] ) - 1 ) * 3 + 1, 1 )

Gotchas and behaviour differences

  • End-of-period comparisons. Qlik's MonthEnd is the last *millisecond*, so OrderTimestamp <= MonthEnd(x) includes the whole last day. EOMONTH and Date.EndOfMonth on a date column give midnight, so a datetime later that day is excluded. Compare < start of next month instead.
  • EOMONTH returns a datetime. Set the column's data type to Date so it relates to your date table.
  • In a date table, the start of month is usually just a column: 'Date'[Month Start]. Add it once there instead of on every fact table.

Official documentation

Related pages

AddMonths(startdate, n [, mode]) AddMonths AddMonths shifts a date by n months. EDATE (DAX) and Date.AddMonths (M) do the same with mode 0 semantics. Mode 1 (end-relative) needs a few extra lines. DAX DATEADD is not the same function. Direct equivalent 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 Date(Date#(text, 'YYYYMMDD'), 'DD-MM-YYYY') Date#() parsing and Date() formatting Date#() interprets text as a date and Date() formats a number as a date, keeping it a dual. In Power BI, parse in Power Query with Date.FromText and set the display format on the column. DAX FORMAT() turns a date into text. 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.