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.
2 thoughts on “Quick DAX: Use GENERATESERIES to create Time buckets”
Very nice article sir.
Hey Phil, I’m so close to figuring out the answer I need. I was wondering if you know if generateseries can be used to count a series of hours between two columns? If not, do you know of a similar function?