Quick DAX: Use GENERATESERIES to create Time buckets

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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:

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:

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.

 

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Philip Seamark on EmailPhilip Seamark on LinkedinPhilip Seamark on Twitter
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.

2 thoughts on “Quick DAX: Use GENERATESERIES to create Time buckets

  • 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?

Leave a Reply

%d bloggers like this: