Quick DAX: Use GENERATESERIES to create Time buckets

A not so obvious use of the recently added GENERATESERIES function to use it to generate time buckets.  The more common use of this function is to generate series of numbers, but it works just as well with Time based data.

Say you want to create a data table with rows that represent time periods.  By passing a TIME value as the <Start>, <End> and <Increment> parameters, you can quickly generate a time bucket table.

The basic calculation is as follows:

Time Table = 
    GENERATESERIES(
        TIME(10,0,0),   -- From 10am 
        TIME(17,0,0),   -- To 5pm
        TIME(0,30,0)    -- Every 30 mins
        )

This will generate the following data table:

A small modification to this calculation to create a “From” and “To” column is as simple as wrapping the GENERATESERIES function with SELECTCOLUMNS in the following way:

Time Table = 
    SELECTCOLUMNS(
            GENERATESERIES(
                TIME(10,0,0),   -- From 10am 
                TIME(17,0,0),   -- To 5pm
                TIME(0,30,0)    -- Every 30 mins
                ),
                "From" , [Value] ,
                "To" , [Value] + TIME(0,30,0)
        )

The output of this updated calculation is now:

The extended version could be joined to other tables to create useful groupings that fall within the range defined by the “From” and “To” values.

Philip Seamark

Phil is Microsoft Data Platform MVP and an experienced database and business intelligence (BI) professional with a deep knowledge of the Microsoft B.I. stack along with extensive knowledge of data warehouse (DW) methodologies and enterprise data modelling. He has 25+ years experience in this field and an active member of Power BI community.