Getting all dates between 2 dates

[ibrahimsharaf]

Hello guys, I have 2 columns named, start date and end date, I want to create a column which contains all the dates between the after mentioned dates, for example

[mohammadyousaf]

take a bow brother.. thank you. 

[Ruepshbedge]

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

[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.

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

[vinicaputo]

Thank you for the information!
You solved a huge problem for me.

[Phil_Seamark]

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])

[ctedesco3307]

This above solution is great - is there a way to get more columns in the calculated table from 'Table 3'  so it's not just the one column? - Thank you 

[ctedesco3307]

Can soemone share the syntax to get those extra columns? this works perfect but I need those extra columns as well. Thank you. 

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Ashish_Mathur]

Hi Phil,

If i am using the PowerPivot in Excel, where exactly do i have to write this formula.  How does one generate a table in the PowerPivot?

[Phil_Seamark]

Hi @Ashish_Mathur

Sorry, that code is more for Power BI Desktop or SSAS Tabular where you can create calculated tables in DAX.  I recommend you follow @ImkeF always excellent suggestions 🙂

[Ashish_Mathur]

Thank you.

[AlexChen]

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

[mbradley-gst]

I've tried replicating this but get the error;

"Expression.Error: The number is out of range of a 32 bit integer value.
Details:
40800.99931"

The start and end date values are datetime rather than date. I've tried swapping them to date but this produces the same error, and ideally I would like to retain the time. Is there a way around this?

All the best,

[Senarath]

Dear AlexChen

I was looking for a similar solution like what you have posted. However, my data has values for each row and those are getting duplicated as the number of rows get duplicated for the date range. So I cannot get a sum of the valyue. e.g. Sum(Cost).

What do you reccommend for that please.

Thanks

Senarath

[inescosta]

Hello,

I am trying to reproduce this solution but I get an error saying Number.from doesn't exist.

[ImkeF]

M is case sensitive. So it should be Number.From

[inescosta]

Can I use M in the table itself or does it have to be in queryeditor? I ask because my table is already changed. the dates shown are from a related table, so those columns don't appear in queryeditor.

Thank you

[ibrahimsharaf]

@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"

[ImkeF]

Not sure if I could follow you here, but the error-message suggests that you are trying to convert a list instead of a number. So is it possible that you haven't expanded the list yet?

Otherwise please share query-code or file.

[ImkeF]

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.