Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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?
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
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
It is not working for the calculated column.
Can you please help me on this one?
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.
In order to help you I need the full query code - could you pls post that here?
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
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.
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 )
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
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.
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]) })
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
Hi,
Thank you for such a prompt reply.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |