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:
CALENDARAUTO( 6)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)