If you have a space symbol in Field or Collection name, put the name in square brackets
[ ] , like this:
[User Stories].[Total Effort]
Basic math operations
Nothing fancy here, usual operators are supported (
*, -, +, /)
(Effort * Risk) / Complexity.Value
Velocity - [Assigned Effort]
There is also
Power(number, power) function in formulas. Note that decimal power is also possible. For example:
Power(10, 2) → 100
Power(4, 0.5) → 2
Access Collection and Fields
Use dot notation to access Collections and Fields:
Epic.Product.Value * [Own Value]
For example, you have Product and several Features linked to every Product. On a Product level you may want to calculate various metrics for Features. Here is where collection aggregates will help:
Sum(Number Field Name)
Avg(Number Field Name)
Max(Number Field Name)
Min(Number Field Name)
For Product Type you may use these examples:
Join(text field, delimiter)
Combines text values from a collection of entities into a single text. For example, returns a string of assignees
Assignees.Join(Name, ', ') → "Michael, Teddy, Jerry"
Counts the number of unique values in a collection of entities
How many teams work on Feature across all User Stories?
In general, you can do operations with Dates like with Numbers. For example, here is how you define the duration of a project in days:
ToDays(Planned.End - Planned.Start)
Note that you can just subtract dates. The result of these operations is a Duration object that has format Duration(days, hours, minutes, seconds). For example, it can be Duration(10, 0, 0, 0) and this is exactly 10 days.
Then you have to apply functions like ToDays or ToHours to convert the duration object into a number of days or hours.
Returns current date.
ToDays(Today() - Planned.Start)
Convert a time duration into a number of days.
ToDays(Planned.End - Planned.Start)
Convert a time duration into a number of hours.
ToHours(Planned.End - Planned.Start)
Convert a time duration into a number of minutes.
ToMinutes(Planned.End - Planned.Start)
Convert a time duration into a number of seconds.
ToSeconds(Planned.End - Planned.Start)
Duration(days, hours, minutes, seconds)
Create a time duration.
Duration(10, 0, 0, 0)
Returns Duration for days.
For example, you want to create a field that will show the date 2 days before the deadline. Later you can use this field to highlight cards on boards. Here is how you do it:
[Due Date] - Days(2)
Returns Duration for hours.
Returns Duration for minutes.
Returns Duration for seconds.
These functions can be used inside collections. Here is the case:
A Feature is decomposed into Stories
The Stories are planned using Sprints
Each Sprint has a start date and an end date
Could I automatically calculate the start and the end date of the Feature to show on a Timeline? Yes, you could:
Feature.[Start Date] = Stories.Min(Sprint.[Start Date])
Feature.[End Date] = Stories.Max(Sprint.[End Date])
Filter inside collections
Filter function (with
OR operators) to extract subsets from collections.
Sum of Effort of all not completed Features:
Features.Filter(State.Final != true).Sum(Effort)
Count of all high complexity non-estimated Features:
Features.Filter(Effort = 0 AND Complexity.Name = "High").Count()
Logic (If, and, or)
You can use
If to create various interesting formulas.
If(condition, true result, false result)
Returns some results based on condition. Here are some examples:
Cycle Time calculation:
If([Planned Dates].End > Today(),
ToDays(Today() - [Planned Dates].Start),
ToDays([Planned Dates].End - [Planned Dates].Start))
Generate some text field based on conditions
If((Confidence > 10) and (Reach > 10),"Important","So-so")
Nest if functions:
If([RICE Score] >= 15,"Cool", If([RICE Score] >= 10,"OK","Bad"))
Generate checkbox (for example, you may create "Fix ASAP" field with this formula)
If((Risk = "High") and (Severity = "High"), true, false)
Checks whether the value is empty
IsEmpty("") → true
Use + to concatenate strings. Some examples:
Name + " " + [Public Id]
Project.Abbreviation + "-" + [Public Id]
"https://www.google.com/maps/search/?api=1&query=" + Address
Note that you can select Text or URL as a formula type.
Calculates the number of characters in the text
Length("This phrase has 29 characters")
Converts the text to lower/upper case
Upper(Name) + " " + Upper(Abbreviation)
Extracts the desired number of starting characters from the text
Left("Fibery rules", 6)
Extracts the desired number of ending characters from the text
Right("Fibery rules", 5)
Removes starting and ending spaces from the text
Checks if text ends with a suffix
EndsWith("Fibery rules", "rules")
Checks if text starts with a prefix
EndsWith("Fibery rules", "Fibery")
Checks if text matches a regular expression
Replace(text, searchFor, replacementText)
Substitutes a matching part of the text
Replace("Fibery is slow", "slow", "fast")
ReplaceRegex(text, regularExpression, replacementText)
Substitutes regular expression matches in the text
ReplaceRegex("It costs $2000", "(\d+)", "1000")
Middle(text, startAt, numberOfCharacters)
Extracts the desired number of characters from the middle of the text
Middle("Umbrella", 5, 4)
Finds the first occurrence of the string in the text
Find("Where's Waldo?", "Waldo") → 8
ToText([number] or [date])
Converts numbers or dates into text
There is no pattern formatting, so you might use this trick
ToText(Day([Creation Date])) + " " + MonthName([Creation Date]) + " " + ToText(Year([Creation Date])) → 21 July 2020
Extracts the full or short month name from a date.
Supported formats: MONTH, Month, month, MON, Mon, mon.
MonthName([Creation Date], "Mon")
Extracts the day-of-week name from a date.
Supported formats: DAY, Day, day, DY, Dy, dy.
Greatest(value1[, value2, value3,…])
Calculates the largest number or the latest date across several values
Greatest(Appointments.Max([Creation Date], Drugs.Max([Creation Date])), Comments.Max([Creation Date]))
Least(value1[, value2, value3,…])
Calculates the smallest number or the earliest date across several values
Least(Appointments.Max([Creation Date], Drugs.Max([Creation Date])), Comments.Max([Creation Date]))