cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ibrahimsharaf
Microsoft
Microsoft

Getting all dates between 2 dates

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

Untitled.png

 

 

 

31 REPLIES 31
TrickMasterPC
New Member

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.

start.PNG

 

 

 

 

 

 

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.

occupancy.PNG

 

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"

expand.PNG

result.PNG

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

Phil_Seamark
Microsoft
Microsoft

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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 

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

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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 🙂


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Thank you.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
AlexChen
Microsoft
Microsoft

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 .

 

1.png

 

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

 

2.png

 

3. after add the new column, expend the list

 

3.png

 

4.png

 

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

 

5.png

 

Best Regards

Alex

 

 

 

 

 

 

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,

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

Hello,

 

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

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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
Microsoft
Microsoft

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

 

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

ImkeF
Super User
Super User

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

I'm getting the following error when I try this approach;

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

The start and end dates are datetime values. I've swapped them to date only and the error persists, ideally I would like to retain the time data aswell. Can you see what I'm doing wrong?

osiel
Frequent Visitor

Hello! Imke Feldmann simply great, I have the same case that ibrahimsharaf indicates, I have been able to replicate the intermediate dates that the formula generates, but I would like to know if it is possible to obtain only the months that remain in between.

Thank you.

 

regards

 

osiel

 

Yes, @osiel,

I've written a function for that here: https://www.thebiccountant.com/2017/12/11/date-datesbetween-retrieve-dates-between-2-dates-power-bi-...

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors