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

[mbradley-gst]

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]

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

[ImkeF]

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

[osiel]

Hello! You are very kind to attend my query so fast, I just feel great! thank you very much. I am working on power query, but I will try to publish it and on purpose I subscribe to your blog. it's great. thank you very much.
I send you a big hug.

Sincerely

osiel

[bipinbhaskarwar]

It is not working for the calculated column.

Can you please help me on this one?

[bipinbhaskarwar]

It is not working for the calculated column.

Can you please help me on this one?

[AshPower]

Hi,

I have a similar situation, where I wanted to get the list of dates between 2 dates. It worked perfectly..Except the the values corresponding to those list of dates are summed together.

May be its the use of 'Number' in , 

{ Number.From([StartDate])..Number.From([EndDate]) }

Is there a way to produce the same result but the values remain intact.

P.S I did use Day/Date, { Date.From([StartDate])..Date.From([EndDate]) }

It  didnt worked for me & gave error

"Expression.Error: We cannot apply field access to the type Function.
Details:
Value=Function
Key=LabEntryDate"

Any help will be greatly appreciated

Thank you.

[ImkeF]

In order to help you I need the full query code - could you pls post that here?

[Anonymous]

Hi,

I want to reproduce the same code but instead of days I need the amount of months between two dates.

Is that possible?

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[ImkeF]

Please check out the code I've provided in the post here: http://community.powerbi.com/t5/Desktop/Get-a-list-of-dates-from-range-and-schedule/m-p/174879#M7641...

It contains a full query code with sample data. Just copy it to the advanced editor.

(If you're not familiar with that, watch this video: https://www.youtube.com/watch?v=S9xlq5KUZ60 )

[Ashish_Mathur]

Hi ImkeF.

I need your help,  I am trying to generate a list of dates between a beginning and an ending date.  When i add your formula by going to Add column > Custom column, i get an error.  When i click on the error, the message is

DataFormat.Error: We couldn't convert to Number.
Details:
    Int. calculation start date=01-Aug-2016

Here is the file.

Thank you for your help.

[ImkeF]

Hi Ashish,

you've uses double square brackets in your formula. This doesn't work in M.

It works when you use this formula:

Table.AddColumn(#"Changed Type1", "Custom", each { Number.From([Int. calculation start date])..Number.From([Int. calculation end date]) })

[Ashish_Mathur]

Hi,

Thank you for such a prompt reply.