Getting all dates between 2 dates

In the query-editor, you can add a column with this formula: { Number.From([StartDate])..Number.From([EndDate]) }

It will create a list with all the desired dates in number format. Just expand that list-column and format to date.

Hi,

I reproduced you error. It happened because the data type of your “StartDate” and “EndDate” are Text, not Date.

Please change them to Date type then follow the steps below.

1, original table .

2. add a new column called “Custom” in query editor

3. after add the new column, expend the list

4. change the data type of “Custom” column to “Date”

Best Regards

Alex

Here are the two most effective methods to create a contiguous range of dates between two date columns such as [Start Date] and [End Date]

Where would I use this? With customer subscriptions, events, classes, hotel stays and more. Essentially anywhere you have a start date and an end date and you need to track and report on the days in between those two dates.

In this scenario we'll be using Hotel Check-in and Check-out dates. You may think... why would I want to do this? The purpose is so that you can look at hotel occupancy or occupancy rates over time, on a timeline (ie Line Chart) by day, week, month, quarter, year etc.

Question?
Should I use DAX to create a calculated table?
OR
Should I use PowerQuery M to create the table?

Answer
Your date range table will be a mere fraction of the size if you import through PowerQuery vs creating it using DAX

• If your end result is less than 5,000 rows you can use DAX, otherwise use PowerQuery
• When you're dealing with tens of thousands, hundreds of thousands or millions of rows always use PowerQuery.
• When tables are loaded from Power Query into the Data Model the VertiPaq engine will exact a high-degree of compression upon the table. There are three types of compression eacted upon the data given the column data type: Value, Dictionary, and Run-Length.
• DAX calculated tables and calculated columns are inefficient. They are not compressed and will lead to larger file sizes and longer refresh times. Your first option should be to include all columns and tables at the source, if not, then in PowerQuery, and only use DAX as a last resort.

DAX Method
Now let's dive into the DAX method. There are other methods out there, but this method I have created performs 50% to 80% faster during the query stage vs all other methods.

• Make sure you have a calendar table in your data model calendar = CALENDARAUTO()
• You have a table 'hotel_guests' with 3 columns
  • [Email] <-- representative of Guest
  • [Check-in Date]
  • [Check-out Date] <-- under a hotel scenario you would subtract 1 day from this. The example formula is not doing this to avoid confusion.

Occupancy Days = 
	SELECTCOLUMNS(	
		GENERATE(
				'hotel_guests',
				DATESBETWEEN(
					'calendar'[Date],
						'hotel_guests'[Check-in Date], 
						'hotel_guests'[Check-out Date]) 
				),
		"Guest Email",[Email], //note the email column comes from the hotel_guests table
		"Occupancy Date",[Date] //note the date column comes from the column output of the datesbetween function
		)

The end result is a two column table with [Guest Email] and [Occupancy Date] where for each guest it includes a row for each night they stayed at the hotel.

PowerQuery M Method

1. Start with the same 3 column table as mentioned above
2. Add a new Custom Column called [Occupancy Date]
3. Input this formula { Number.From([#"Check-in Date"])..Number.From([#"Check-out Date"]) } <--note Number.From converts the Date value to an ISO date so 7/1/2022 becomes 20220701 as an INT.64
4. The custom column generates a list (array) of dates between check-in and check-out for each row in your source table
5. Expand the column list values to "New Rows"
6. Remove the original [Check-in Date] and [Check-out Date] columns (unless you need them for some reason)
7. Format the [Occupancy Date] column as a data type of "Date"

As a test you can employ both methods (in separate PBIX files), and benchmark the resulting file size of your PBIX files, as well as, computational and refresh run times. The PowerQuery method will provide better overall results.

Looking to learn more? I teach on weekends for Divergence Academy. We're always running classes

ImkeF it works, but when I expand the lists, the primary key in my table gets duplicated, so Power BI gives me an error when applying the query, I tried creating a new table, adding a new custom column with the formula you mentoined in addition of the table name besides the column name, but it gives me this error in every row of the created column

"DataFormat.Error: We couldn't convert to Number.
Details:
List"

Hi ibrahimsharaf

Here is a DAX based solution.  This is a calculated table and just replace the Table3 with the name of your table.

New Table = 
            SELECTCOLUMNS(
                FILTER(
                    CROSSJOIN('Table3',CALENDARAUTO()),
                   'Table3'[EndDate] >= [Date]
                   && 'Table3'[StartDate] <= [Date]
                   ),
                   "Date",[Date])

it's possible if anyone needs please let me know. I will mail solution.

take a bow brother.. thank you.