Quick DAX: Use GENERATESERIES to create Time buckets

Posted by on Jun 21, 2018 in DAX, Power BI | One Comment
Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

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.

 

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
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.

One Comment

Leave a Reply

Your email address will not be published. Required fields are marked *