Forum Discussion
Getting all dates between 2 dates
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.
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.
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 )
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.
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]) })
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?
Hi,
Share some data and show the expected result.
