Functions

The avg() function calculates the average of the values in the specified time series from range_start to range_end.

// Example
avg("Revenue"[-3:0])

The avg_ytd() function returns the average of the values in a time series from the beginning of the fiscal year until and including the current month.

// Example 
avg_ytd("Revenue")

The avg_last_year() function returns the average of the values in the previous fiscal year. This function is influenced by your fiscal year settings.

// Example
avg_last_year("Revenue")

The avg_last() function returns the average of the values in the time series going back n months, as defined by the num_months parameter.

// Example
avg_last("Revenue",6)

The median() function returns the median of the values in the specified time series from range_start to range_end

// Example
median("Revenue"[-3:0])

The median_ytd() function returns the median of the values in a time series from the beginning of the fiscal year until and including the current month.

// Example
median_ytd("Revenue")

The median_last_year() function returns the median of the values in the previous fiscal year. This function is influenced by your fiscal year settings.

// Example
median_last_year("Revenue")

The median_last() function returns the median of the values in the time series going back X months, as defined by the num_months parameter.

// Example
median_last("Revenue", 6)

The sum() function calculates the total of the values in the specified time series from range_start to range_end.

// Example
sum("Revenue"[-3:0])

The sum_ytd() function returns the sum of the values in a time series from the beginning of the fiscal year until and including the current month.

// Example
sum_ytd("Revenue")

The sum_last_year() function returns the sum of the values in the previous fiscal year. This function is influenced by your fiscal year settings.

sum_last_year("Revenue")

The sum_last() function returns the sum of the values in the time series going back X months, as defined by the num_months parameter.

sum_last("Revenue", 6)

The receivables() function accumulates revenue amounts from previous months based on an assumption of payment days. Based on the number of payment days, the function loops back through previous months to collect revenue as part of receivables.

The function assumes 30 days per month. The payment days input must be hardcoded and cannot reference a cell.

// Example
receivables("Revenue", 30)

Theignore_div_zero() returns a zero if the function’s output is a #DIV!/0 error. The function is used for margin and percentage calculations, where the denominator is occasionally zero.

// Example
ignore_div_zero("Direct cost"[0] / "Revenue"[0])

The power() function is a mathematical function that returns the value of the parameter num to the power of the exponent parameter.

// Example
power(10,2)

The min() function returns the smallest numeric value of the input values.

// Example
min("Revenue"[0],"Revenue"[-1])

The max() function returns the largest numeric value of the input values.

// Example
max("Revenue"[0],"Revenue"[-1])

The abs() function returns the absolute value of a number.

// Example
abs("Direct costs"[0])

The round() function rounds the number value up or down to the nearest integer. Optionally, provide desired number of decimals.

// Example
round("Revenue"[0],2)

The round_down() function rounds numbers down to the nearest integer.

// Example
round_down("Revenue"[0])

The round_up() function rounds numbers up to the nearest integer.

// Example
round_up("Revenue"[0])

The if() function allows you to create a conditional calculation, similar to what is available in Excel or Google Sheets.

// Example
if("Revenue"[0]=0,0,"Gross profit"[0]/"Revenue"[0])

If revenue is zero, display zero; otherwise, calculate the gross profit margin. This specific example prevents #ERRs from dividing by zero.

The if_month function returns 1 if the time period is equal to the month number input in the formula, else 0.

• if_month(1) returns 1 if the month is January, else 0

• if_month(1,6) returns 1 if the month is January or June, else 0

• if_month(1,6,12) returns 1 if the month is January, June, or December, else 0

The if_quarter_month function returns 1 if the time period is equal to the month number inside a given quarter. E.g., you can input [1;3] into the formula.

• if_quarter_month(1) returns 1 if the month is January, April, July, or October

• if_quarter_month(2) returns 1 if the month is February, May, August, or November

• if_quarter_month(3) returns 1 if the month is March, June, September, or December