Creating a List of Numbers or Dates in Power BI using GenerateSeries Function in DAX

If you ever need to create a list of numbers (either decimal or whole number), or list of dates and times, there is a very simple and useful function in DAX which helps. GenerateSeries is a simple function to use to create a list. In this article, I’ll explain how you can use this function. If you want to learn more about Power BI, read Power BI book from Rookie to Rock Star.

Table Generators

There are a set of functions in DAX which generates a table. Some of these functions are from the form of table constructors, I have written about the Table Constructor in DAX, and also the DataTable() function.

There is another set of functions to generate a table. I have written about Calendar() and CalendarAuto() functions and explained how they can be used to create a table with a list of dates. In this article, I am explaining the GenerateSeries() function in DAX and how you can create a table with it.

GenerateSeries

GenerateSeries is a function in DAX that generates a list of values. The list starts from a Start value and ends at an End value. You can also specify an increment. However, the increment value is optional, and if you don’t set that value, the default increment would be 1.

GenerateSeries(<start value>,<end value>,[increment value])

Sample Usage: Creating List of Numbers

GenerateSeries is a simple function to use, you can easily use it with the expression below:

Sample Table = GENERATESERIES(1,10)

The result would be a table with values from one to ten, incrementing one at a time (the default increment value is one).

Your numbers can be even negative as below;

Sample Table = GENERATESERIES(-3,3)

Changing the Increment Value

You can also change the increment value to whatever you want.

Sample Table = GENERATESERIES(0,10,2)

The start and end value or/and the increment value can be also decimal values.

Sample Table = GENERATESERIES(1.0,3.0,0.4)

You might, however, need to set the number of decimal place characters to the right value to see the effect.

List of Dates

GenerateSeries is not just for numeric values. It also works for date values. Here is an example:

Sample Table = GENERATESERIES(
DATE(2019,10,1),
DATE(2019,10,15)
)

The default increment is one value, which for the data type of DateTime means one day. You can, however, change it to weekly or any other durations with changing the increment:

Sample Table = GENERATESERIES(
DATE(2019,10,1),
DATE(2019,10,15),
7
)

If you are looking for other ways of creating a list of dates, check out my article about Calendar() and CalnedarAuto() functions in DAX.

List of Times

You can also generate a list of Times, using the same function;

Table = GENERATESERIES(
Time(1,0,0),
TIME(2,0,0),
1/24/60/60)

Change the increment

However, for the TIME value, you need to set the increment, because the default is one day, and that won’t affect the time over 24 hours! If you want to get to other time portions you can follow this approach:

• 1: means a day: 24 hours
• 1/24: means one hour
• 1/24/60: means one minute
• 1/24/2: means a half hour
• 1/24/60/60: means one second
• …

And you can build all types of other combinations. Below is a table of times, adding one second at each row;

List of Currency Values

Table = GENERATESERIES(
CURRENCY(100.30),
CURRENCY(110.1),
CURRENCY(0.5)
)

GenerateSeries is used when you create a What If Parameter in Power BI

When you create a What If Parameter in Power BI, behind the scene, the GenerateSeries function is used:

The What If Parameter is a list of values in DAX which is generated using the GenerateSeries function.

Summary

In summary, if you want to create a list of values in Power BI using DAX, GenerateSeries is a great function to do that. It works not only with numeric values but also with date and time values.

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.