QlikToDax

Interval(EndTime - StartTime, 'hh:mm')

Age, Interval and date differences in Power BI

In Qlik, dates are numbers, so End - Start is a number of days and Interval() just formats it. DAX DATEDIFF counts boundaries crossed, not completed units. DATEDIFF(dob, today, YEAR) isn't an age, so use a completed-years formula for Age(). DAX has no duration type. Store durations as numbers, such as minutes or hours, and format them for display.

Partial / different approach DAX Power Query

Short answer: Store the duration as a number, for example Minutes = (T[End] - T[Start]) * 1440, and aggregate that. Format "hh:mm" only at the end. For Age, see the formula below, not DATEDIFF(..., YEAR).

What it does in Qlik

  • Age(timestamp, date_of_birth) returns completed years.
  • Interval(number [, format]) formats a day count as a duration, for example 'hh:mm', 'D hh:mm' or 'mm:ss'.
  • Because the result is a dual, totals still add up.

Qlik example

Qlik script and chart
Age(Today(), BirthDate)                    AS Age,
Interval(EndTime - StartTime, 'hh:mm')     AS Duration,
// chart: total duration
Interval(Sum(EndTime - StartTime), 'D hh:mm')

Power BI equivalent

DAX

DAX
Age =
VAR d = TODAY ()
VAR b = Person[BirthDate]
RETURN YEAR ( d ) - YEAR ( b )
    - IF ( MONTH ( d ) * 100 + DAY ( d ) < MONTH ( b ) * 100 + DAY ( b ), 1, 0 )

Minutes = ( T[EndTime] - T[StartTime] ) * 1440                // column, exact like Qlik End - Start

Duration hh:mm =                                             // measure, for display
VAR m = ROUND ( SUM ( T[Minutes] ), 0 )
RETURN IF ( NOT ISBLANK ( m ),
    FORMAT ( INT ( m / 60 ), "0" ) & ":" & FORMAT ( MOD ( m, 60 ), "00" ) )

Power Query

Power Query M
= Table.AddColumn(T, "Minutes", each Duration.TotalMinutes([EndTime] - [StartTime]), type number)

Gotchas and behaviour differences

  • DATEDIFF(DATE(2025,12,31), DATE(2026,1,1), YEAR) returns 1. It counts year boundaries crossed, not completed years. The same applies to MONTH, WEEK and even MINUTE: DATEDIFF from 10:00:59 to 10:01:00 returns 1 minute. Subtract the timestamps and multiply by 1440 for exact minutes, as Qlik's End - Start gives.
  • Durations over 24 hours: an "hh:mm" format string on a time value wraps at 24 hours, so 26 hours shows as 02:00. Qlik's Interval() would show 26:00. Build the text from total minutes, as above. The result is text, so keep a numeric measure for sorting and charts.
  • Power Query has a real duration type, but the model loads it as a decimal number of days. Convert to minutes or hours explicitly so the unit is clear.

Official documentation

Related pages

NetWorkDays(start, end [, holiday...]) NetWorkDays (working days) NetWorkDays counts Monday-to-Friday working days between two dates, minus listed holidays. DAX has NETWORKDAYS with configurable weekends and a holiday table. A working-day flag on the date table is often the more maintainable option. Direct equivalent Floor(OrderTimestamp) AS OrderDate Splitting timestamps into date and time Qlik developers split timestamps with Floor() for the date and Frac() for the time, partly to save memory. Do the same in Power Query with DateTime.Date and DateTime.Time. It makes the date join to the calendar table and reduces column cardinality in VertiPaq, so the reason is the same in both tools. Direct equivalent Num(Sales, '#,##0.00') Num, Text and number formatting In Qlik, Num(x, '#,##0.00') returns a dual: the number stays sortable and summable and only the display changes. DAX FORMAT() returns text, which breaks sorting, aggregation and chart axes. The right translation is usually a measure or column format string, not a function. Use FORMAT only for labels. 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.