Value.Subtract
ValueReturns the difference of two values, with support for numbers, durations, and date/time types.
Syntax
Value.Subtract(value1 as any, value2 as any, optional precision as nullable number) as anyParameters
| Name | Type | Required | Description |
|---|---|---|---|
value1 | any | Yes | The value to subtract from (number, duration, datetime, etc.). |
value2 | any | Yes | The value to subtract. |
precision | number | No | Controls floating-point precision. Defaults to Precision.Double. |
Return Value
any — The difference of value1 and value2.
Remarks
Value.Subtract returns the difference of value1 and value2. It is the functional equivalent of the - operator in M — Value.Subtract(a, b) produces the same result as a - b.
This function supports type coercion across compatible types:
- number - number — standard numeric subtraction, returning a number.
- duration - duration — subtracts one duration from another, returning a duration.
- datetime - duration — shifts a datetime backward by the given duration, returning a datetime.
- datetime - datetime — computes the elapsed time between two datetimes, returning a duration.
- date - duration — shifts a date backward by the given duration, returning a date.
- date - date — computes the elapsed time between two dates, returning a duration.
- time - duration — shifts a time backward by the given duration, returning a time.
- time - time — computes the difference between two times, returning a duration.
- datetimezone - duration — shifts a datetimezone backward, returning a datetimezone.
- datetimezone - datetimezone — computes the elapsed time, returning a duration.
The optional precision parameter controls floating-point precision for numeric operations. By default, Precision.Double is used. You can specify Precision.Decimal for higher-precision decimal arithmetic.
If the two values are of incompatible types, an error is raised.
Examples
Example 1: Calculate days since order
let
RefDate = #date(2024, 3, 1)
in
Table.AddColumn(
Table.SelectColumns(Table.FirstN(Sales, 5), {"OrderID", "Product", "OrderDate"}),
"DaysAgo", each Duration.Days(Value.Subtract(RefDate, [OrderDate])), type number
)Output
OrderID | Product | OrderDate | DaysAgo | |
|---|---|---|---|---|
| 1 | 1 | Widget A | 1/15/2024 | 46 |
| 2 | 2 | Gadget B | 1/20/2024 | 41 |
| 3 | 3 | Widget C | 2/1/2024 | 29 |
| 4 | 4 | Gadget D | 2/15/2024 | 15 |
| 5 | 5 | Widget A | 3/1/2024 | 0 |
Example 2: Subtract two durations
Value.Subtract(#duration(10, 0, 0, 0), #duration(3, 12, 0, 0))Output
Result | |
|---|---|
| 1 | 6.12:00:00 |
Compatibility
✓ Power BI Desktop✓ Power BI Service✓ Excel Desktop✓ Excel Online✓ Dataflows✓ Fabric Notebooks