GENERATESERIES
Returns a one-column table of numbers from a start value to an end value.
GENERATESERIES(<start>, <end> [, <increment>])Returns: A table with one column, named Value.
What it does
GENERATESERIES creates a list of numbers. Use it for a what-if parameter, such as a discount from 0 to 20 percent. It also builds number bands for grouping amounts, and a sequence of month numbers. The result is a one-column table, so it can feed a slicer or a join.
Syntax and arguments
- start: the first number in the list.
- end: the last number. It is included when the steps land on it.
- increment (optional): the step between numbers. The default is 1.
Example
Amount bands for grouping invoices by size, as a calculated table. Each row is the start of a band.
Amount Bands =
GENERATESERIES ( 0, 100000, 25000 )
The list holds the values from 0 to 100,000 in steps of 25,000. A measure can then count the invoices in each band, using a comparison against the band's value.
Example: a second use
A what-if discount slider from 0 to 20 percent, in steps of 5 percent.
Discount Scenarios =
GENERATESERIES ( 0, 0.2, 0.05 )
Sales After Discount =
VAR Discount = SELECTEDVALUE ( 'Discount Scenarios'[Value], 0 )
RETURN
[Total Sales] * ( 1 - Discount )
A slicer on the Value column chooses the discount, and the measure applies it. A report built this way lets a controller test a price change without changing the source data.
Things to watch
- Decimal steps can show tiny rounding differences. Wrap the measure in
ROUNDwhen the figure must be exact. - A large range creates a large table. Keep the list short.
- The table is not related to the sales data. A measure has to use it on purpose.
- Check whether the end value is included in your version and test with a small range.
- For a list of values that are not a regular sequence, use
DATATABLE.
In business terms
Sensitivity tables for price or volume changes, bands for aging or collections work, and month number lists for a fiscal calendar. The same list of steps can drive a what-if slider in a board forecast.