Forum Discussion
Microsoft EmployeeGetting all dates between 2 dates
Community ChampionIn 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.
It is not working for the calculated column.
Can you please help me on this one?
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
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#M76419
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
Super User
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-2016Here is the file.
Thank you for your help.
- ImkeF
In order to help you I need the full query code - could you pls post that here?
Hi,
I want to reproduce the same code but instead of days I need the amount of months between two dates.
Is that possible?
It is not working for the calculated column.
Can you please help me on this one?
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-power-query/
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
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?
