How to create a column based on a date range in Power BI?

Here’s a question from David:

Is there a Power BI function that allows to create a table showing specific values for a date range? Let’s assume that i am interested to create a dummy table showing sales figures for all days in July 2023. Any ideas on how to accomplish that.

Add a column based on a data range

Proceed as following to create a Power BI column containing dates in a specific range:

  • Launch Power BI Desktop and open your Power BI report.
  • Navigate to the Table View.
  • Create a new table containing entries for all dates in the range between July 1st to July 31st 2023:
July Sales = CALENDAR (DATE (2023,7,1), DATE(2023,7,31))
  • You will see that your table contains a Date column that has the date range data.
  • At this point, you can add additional columns to your table.
  • For example, you can create a column containing random sales values using the following code:
Amount = RANDBETWEEN(150000, 450000)
  • Here is an extract from the table we just created:

Creating a data range column from your Power BI model

You can use the CALENDARAUTO function to pull a data range column based on your existing data model.

The following expression creates a table showing a year date range starting July 1st:


I can filter the calendar table and show just the data range pertaining to July dates using the DAX FILTER function:

July_Sales+2 = FILTER(CALENDARAUTO( 6), MONTH([Date]) = 7)

Follow up learning

How to find rows by multiple condition in Power BI tables?