QlikToDax

Sum(If(InYear(OrderDate, Today(), -1), Sales))

InYear, InMonth and period flags in Power BI

Qlik developers often put InYear() and InMonth() flags in the script (-1 or 0) and multiply by them in charts, or use them inside If(). In Power BI, the equivalent is a calendar table with offset columns (YearOffset, MonthOffset), filtered with CALCULATE or a relative-date slicer. Time-intelligence functions such as SAMEPERIODLASTYEAR cover the common cases.

Partial / different approach DAX Model

Short answer: Add Year Offset = YEAR('Date'[Date]) - YEAR(TODAY()) to the calendar. Then Sales LY = CALCULATE([Sales], 'Date'[Year Offset] = -1). Or, relative to the selection, CALCULATE([Sales], SAMEPERIODLASTYEAR('Date'[Date])).

What it does in Qlik

  • InYear(timestamp, base_date, period_no [, first_month_of_year]) returns True (-1) if the timestamp is in the year containing base_date, shifted by period_no.
  • InQuarter, InMonth, InWeek and InDay work the same way. The ...ToDate variants (InYearToDate) stop at base_date.
  • first_month_of_year handles fiscal years.

Qlik example

Qlik script and chart
// script flags in the calendar
-InYear(Date, Today(), 0)  AS IsCurrentYear,
-InYear(Date, Today(), -1) AS IsLastYear,
-InMonth(Date, Today(), -1) AS IsLastMonth
// chart
Sum(Sales * IsLastYear)
Sum(If(InYear(OrderDate, Today(), -1, 4), Sales))   // fiscal year from April

Power BI equivalent

Calendar offset columns

DAX
Year Offset  = YEAR ( 'Date'[Date] ) - YEAR ( TODAY () )
Month Offset =
    ( YEAR ( 'Date'[Date] ) - YEAR ( TODAY () ) ) * 12
    + MONTH ( 'Date'[Date] ) - MONTH ( TODAY () )
Fiscal Year = YEAR ( EDATE ( 'Date'[Date], -3 ) )     // FY starts in April, labelled by start year

Measures

DAX
Sales last year (calendar) = CALCULATE ( [Sales], 'Date'[Year Offset] = -1 )
Sales last month           = CALCULATE ( [Sales], 'Date'[Month Offset] = -1 )

// relative to whatever the user selected, not to today
Sales SPLY = CALCULATE ( [Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

Gotchas and behaviour differences

  • Calculated columns are computed at refresh, so offsets based on TODAY() are only as fresh as the last refresh. That's the same as script flags in Qlik. If you need "today" at query time, compare against TODAY() inside the measure instead.
  • Filtering 'Date'[Year Offset] = -1 replaces any user filter on Year Offset, but keeps filters on other date columns such as Month. If a Month slicer is active, wrap it as CALCULATE([Sales], 'Date'[Year Offset] = -1, REMOVEFILTERS('Date')). That clears every date filter, like {<Year=, Month=, Date=>} in Qlik. Qlik's {<Year=>} alone would clear only Year.
  • Time-intelligence functions need a proper date table marked as one. See master calendar.

Official documentation

Related pages

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 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 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 Sum({1} Sales) / Sum({<Region=>} Sales) Ignoring selections: {1}, Field= and {1<...>} How to translate Qlik's {1} set identifier and empty field modifiers (Region=) into REMOVEFILTERS, ALL and ALLSELECTED. Includes the important difference in how chart dimensions are treated. 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.