Creating a Dynamic Date Table in Power BI

Would you like to learn a little trick for creating a date table from an existing dataset in Power BI? Let me show you (also check out my brief video demo included here).

• I have a dataset with a column called PermitDate.
• The first thing I need to do is create a new query and a blank query that references this existing data set. To do this, I type an equal sign (=) in the code bar followed by the name of that table, in this case Permits.
• To make a date table from this column I need the range of dates, so I need to pull out the min and max date. I can use the GroupBy function to do this.
• Before I GroupBy, I’ll create a custom column to help. I’ll name it GroupBy and my formula is the number 1. I use a single number, this way each row has the same and when we GroupBy that row, we will just get one row result.
• Next, to pull out the min and max date I’ll use the aggregation tools in the GroupBy function. I set up two new columns for MinDate and MaxDate and I want to run a min and max operation on that PermitDate column.
• When I run this, I get one row showing the minimum and maximum date range for that data set.
• I want this range to be broader and extend the minimum to the beginning of the month and the maximum to the end of the month. I do this by using some of the built-in functions in the M Query mode within custom columns. I’ll do one for start date and end date and the IntelliSense will kick in and help here.
• Now I want to create a list of all the dates in between those min and max dates. I’ll add one more custom column and I’ll use the NumberFrom function. This will take our dates and turn them into the numerical version.
• But I want to get all the dates, so to create a list I’ll use brace characters (you may call them curly brackets) to set a list and use two NumberFrom formulas to draw the number from the start date and to the end date.
• How do we make that list know to go from the start date to the end date? That little piece of magic is easily done by adding two dots (..) between my start date and end date formulas.
• Now when I execute this, my date column will turn into a list. Then I can remove all those other columns I created, and I have a list of all the dates between the beginning and end dates on my table. It is showing them in that numerical form, but I want to see them in date form. So, I simply go under Transform in my tool bar and under data type I select the date format.
• I can use that same process to add more months to this and build a date table.

That is a quick way to build a table that always references the range of dates within your existing table. As this table grows, our date table will continue to grow along with it. For you visual learners out there, check out my video to see my demo. I hope this quick tip was helpful.

If you’re looking to learn about how you can leverage Power BI or any product or service within Azure, you’re in the right place. Our team of experts can help, no matter where you are on your Azure journey. Contact us or click the link below to learn more of how we can help you take your business from good to great.