AddMonths(startdate, n [, mode])

AddMonths in Power BI

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

Short answer: Use EDATE(d, n) in DAX and Date.AddMonths(d, n) in M. Both clamp 31 January + 1 month to the end of February, like Qlik's default mode 0.

What it does in Qlik

AddMonths(startdate, n [, mode]) adds n months (negative to subtract). In mode 0 (default), the day number is kept where possible and clamped to the month's last day. In mode 1, for start dates on or after the 28th, the result keeps the same distance to the end of the month: 28 February 2026 + 2 months with mode 1 gives 30 April (in a leap year, 28 February is one day from the end, so it gives 29 April).

Qlik example

Qlik script

LOAD ContractStart,
     AddMonths(ContractStart, 12)    AS ContractEnd,
     AddMonths(ContractStart, -1, 1) AS PrevMonthEndRelative
FROM [lib://Data/contracts.qvd] (qvd);

Power BI equivalent

Mode 0 (default)

DAX

Contract end = EDATE ( Contracts[ContractStart], 12 )

Power Query

Power Query M

= Table.AddColumn(Source, "ContractEnd", each Date.AddMonths([ContractStart], 12), type date)

Mode 1 (relative to month end)

DAX

Prev month end-relative =
VAR d = Contracts[ContractStart]
VAR DaysToEnd = INT ( EOMONTH ( d, 0 ) - d )
RETURN
    IF ( DAY ( d ) >= 28, EOMONTH ( d, -1 ) - DaysToEnd, EDATE ( d, -1 ) )

Gotchas and behaviour differences

DATEADD in DAX is a table function for time intelligence (it shifts a set of dates in the filter context). It cannot add months to a single value.
Microsoft's EDATE documentation doesn't say what happens to a time part, so convert datetimes to dates first if the time matters. Date.AddMonths on a datetime keeps the time.

Official documentation

addmonths - script and chart function (Qlik Help)
EDATE function (DAX) (Microsoft Learn)
Date.AddMonths (Power Query M) (Microsoft Learn)
DATEADD (table function) (dax.guide)

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 Sum({<Year={$(=Max(Year))}>} Sales) Dollar-sign expansion in sets: $(=Max(Year)) Dollar-sign expansions such as $(=Max(Year)) put a calculated value into a set expression. In DAX you use a variable, but it is evaluated per cell, not once per chart. Partial / different approach 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

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.