Tutorial: Fill All Dates Between Start Date and End Date Columns (Using DAX or PowerQuery M )

[TrickMasterPC]

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.

In Power BI Desktop, on the "Home" ribbon click on "New Table" and paste in the below DAX to create your calculated table.

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

[Sorinamsm]

Would you kindly advise how I can achieve similar results, but with year_quarter? Like 2024_1, 2024_2... Etc. 

Thank you

[v-zhangti]

Hi, @TrickMasterPC 

Thank you for sharing! You can Mark your post as an answer, so it will be easier for other users to search.

Best Regards