Generate Dates between Start and End Date in Power Query

[@GoodlyChandeep]

Consider taking a look at all the solutions. www.goodly.co.in/generate-dates-in-rows-between-start-end-date/ Most of them are better than mine:) Kudos to everyone who contributed. Cheers Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/

[@isidrohernandezescobar8972]

Thank you. For all of you out there. I made a calendar view in sharepoint to post day-off and shutdowns, i needed to retrieve those days to exclude and calculate available days for production. All i needed is in the first 5 minutes of this vid.

[@erikbohn2771]

Mind blown!!🤯 I did not know you could nest a let in the custom column editor!

[@rajesm106]

I don't usually post comments but this definitely earned an exception! 😀So well explained! I saw another video that showcased the same content, but I just could not replicate it in my use case. Thank you! Do keep up the great work!

[@GoodlyChandeep]

I am sorry It didn't work for you :(

[@amitk1208]

this solution helped me a lot fortunately i saw this video before 3-4 days when i got a task of 73 persons i had to make a date list of months according to their date of birth from 2006 to 2022 for every person of 73. i downlaoded the excel file from ur site and put my values into that table othewise would have been writing a vba code for this.

[@bhushanbangar3025]

Thanks for the solution. With the help of your video, I did it in simple steps. = Table.AddColumn(#"Changed Type", "Absence_Date", each let AllDates= {Number.From([AbsenceStart])..Number.From([AbsenceEnd])} in List.Transform(AllDates, each Date.From(_)))

[@user-eu3bw2os5h]

Thank you so much!!! You're a life saver! The code in video didn't work with me but your code did!

[@user-eu3bw2os5h]

Do you know how to do this in DAX instead of power query? I believe it will take less time.

[@felipemartins3535]

@@user-eu3bw2os5h DAX not allow us to manipulated data as Power Query, only measures.

[@robovac3557]

One of the best explained videos of any topic I've ever watched. TY.

[@Jerry-hg1fq]

You're a legend! Been banging my head trying to solve this one for a couple of days. This made it so much simpler than what i had come up with. Thank you!

[@aaksamuel]

Hey you man, unbelievable, you are doing a great job. Data scientists are adding value to the Engineering community.

[@rajivshah2761]

Thank you so much! I’ve been trying to find a way to do this for ages. Your videos are always super helpful.

[@tug_van_tuggles]

Thanks so much. I didn't need to return the true day the start of the month was fine for me so I could skip a section. This was just what I need to do a monthly date range and forecasted stock levels, over time. You explained it really well saved me some time, and if I can end results saves the teams I work with time as well. 🙂

[@willemmartens5898]

I've been searching for so long to find something like this! Thanks!

[@jackmcdonald6686]

Honestly such a great resource. Very well explained and made my life so much easier. Thank you.

[@wayneedmondson1065]

Hi Chandeep. Awesome solution! It's still a bit beyond me to do it all in one formula, so my solution partially relied on the UI. Nice to see your method that looks under the hood.. something to aim for, as I increase my skills. One nice thing about PQ is that you can still get a lot of work done with the UI, while you learn your way into building formulas directly for even more power and versatility. Thanks again for the challenge and inspiration to learn and create :)) Thumbs up!!

[@GoodlyChandeep]

I agree the UI can take you far. I used PQ for more than 2 years before I started to dabble in M

[@paspuggie48]

Another fantastic use of nested functions Goodly. Brilliant!

[@JustRandomExplorer]

Great content again! Just what I needed for my business problem!

[@scottymac734]

Thorough, excellent presentation. I first found this concept on the Nate Chamberlain blog, and another in a post by Miguel Escobar, which generally serve my purpose. But this greatly-expanded explanation opens the door to new, future uses. Much appreciation to all those who take time to share their knowledge with others. This is the most thorough explanation I've found on this topic.

[@GoodlyChandeep]

Glad it was helpful!

[@parvindersingh7152]

H Chandeep, slightly different approach in 1 step: combines 2 lists , 1 for the first date which might not be the first of the month and the second which combines dates from month 2 till the end . List.Combine( { List.Transform({Number.From([StartDate])}, each Date.From(_)), List.Distinct( List.Transform( { Number.From(Date.EndOfMonth([StartDate])) + Duration.Days(#duration(1, 0, 0, 0)) .. Number.From([EndDate]) } , each Date.StartOfMonth(Date.From(_)) ) ) } )

[@Sonampillai]

This is exactly what i was looking for...thanks for this...much needed..

[@GoodlyChandeep]

Glad it was helpful!

[@samirakhoshahval2704]

Super clear tutorial! Thank you so much.

[@InonoYazy]

Thank you so much! It would be great to see in the end the video how would you use the list in the visual.

[@w13ken]

Hi Chandeep, this is a genius solution to a problem that has been foxing me for too long! Many thanks for your brilliantly explained solution...Ken (a happy new subscriber) 😀

[@GoodlyChandeep]

Welcome Ken!

[@sofiea.7977]

Here is the code used in the video: let stdt = [Start_date], AllDates = {Number.From([Start_date])..Number.From([End_date])}, StofMonthDates = List.Distinct( List.Select( List.InsertRange( List.Transform( AllDates, each Date.StartOfMonth(Date.From(_)) ), 0, {[Start_date]} ), each Number.From(_) >= Number.From(stdt) ) ) in StofMonthDates

[@user-eu3bw2os5h]

Why this is not working with me 😭

[@joevanbedico-cn1rq]

Have almost same solution, but instead of using List.InsertRange I just skip the first row of the list and append the start date.. {[Start Date]}&List.Skip(List.Distinct(List.Transform... Btw, thank u for sharing ur knowledge I'm currently on this video, watching all ur PQ playlist from the start. Next will be the DAX 😁

[@marcskerritt]

This video was awesome. Really good stuff. I'm curious though, how could you create start and end date columns if you have a column with "effective date" and that's the date that a price went into effect for a specific item in a specific location.

[@zahoorsarbandi2982]

Very nice.. There are too much for learning in the video. well explained. Well Done!!

[@GoodlyChandeep]

Glad you think so!

[@MishkyGammy]

Chandeep, this is amazing. Was looking for solution to a similar problem. Thanks!

[@matateu2006]

Thanks a lot for this video. You are a life saver!!!

[@GoodlyChandeep]

Glad it helped!

[@ShawnKnowles]

Excellent explanation and walkthrough.

[@GoodlyChandeep]

Glad you enjoyed it!

[@orkhannazarov2781]

Thank you very much. Awesome solution and great explanation.

[@GoodlyChandeep]

Glad you liked it Orkhan !

[@felipemartins3535]

You're amazing man! I'm thank you so much.

[@vimalpatel628]

Great video, thanks very much this helped me with a problem

[@GoodlyChandeep]

Glad it was helpful 😊

[@williamarthur4801]

I found that pretty tricky, , have you any more information on declaring variables in pq, I got in a bit of muddle seeing if it mattered where abouts I put it, I also noticed in the Editor htat I had two 'in' statements, all worked in the end and a really good exercise.

[@mdtauhidalam9349]

Hey @GoodlyChandeep, I am a beginner in the world of Power BI and I am stuck in one of my projects, where I have to deal with a similar kind of situation, where I have quite a lot of nulls in the EndDate column. When I follow this approach to get every single date between the start and end date, it is throwing and error saying "cannot convert the value null to type number.." Can you give a bit of a hint so that I can resolve the problem? I've been following you for a weeks now and I find your content super helpful. I've learnt a lot from your youtube videos, thank you so much! 🙏🏻

[@santoshkamala2750]

Hi Chandeep, loved the way you explain. However, I have a challenge. In the above example - I want to give a solution in Power BI to my user where, user will choose a date from calendar picker - Example below: I have two date columns in my table (Start Date and End Date). I want to give a calendar picker to my user in visualization page. Based on the date selected by my user, I want to calculate two columns: First column: "How many days completed?" Formula: Selected Date - Start Date + 1 Second column: "How many days to GO" ? Formula: End Date - Selected Date + 1 Is it possible ? Initially, I thought to keep dates as parameter. But we don't have DATE datatype. Please help.

[@edtrc]

Nice video, just what I was looking for!

[@pabsmart]

thanks a lot mate, you're the M Wizard!

[@GoodlyChandeep]

Glad you think so !

[@williamarthur4801]

Regarding List.Select ; if you name the function rather than using 'each' I found I didn't have to make the start date a 'variable', List.Select ( list , (B)=> B >= [Stard Dates] ) , seemed to work fine. If I used each or (_)=> _ , then the saved start date name was required.

[@IvanCortinas_ES]

Excellent explanation. Fantastic, as always!!!

[@GoodlyChandeep]

Thank you!

[@alexhewish5125]

Great tutorial, can't wait to try it out. What if the start date was the 31st, not all months have 31 days so that could create an issue maybe?

[@gingermo]

The guru! Wow, just wow!

[@GoodlyChandeep]

Thank You !

[@anormans8252]

Thank You Goodly, now i've been searching solution of how to extract the dates and time range become two shift, shift 1 started at 6:00 ended at 18:00 and shift 2 started at 18:00 ended at 6:00 on the next day. Hope anyone can give me the video link if any.. thank you very much

[@mirrrvelll5164]

Good one! But what if you got an example where you have the end of the year and the beginning of next? like, your vacay is from 20.12.2023 - 5.01.2024? How would you approach that? Got any video on that topic maybe?

[@kennethstephani692]

Terrific Video, Chandeep!!!

[@juandavidaltamar7911]

Hi excelent video. However i was wondering whether it is possible to generate the secuence of data entries considering that you have a restriction such as quarter o semesters or any oder combination. To put it in context of this video it would be something like this: An investor put money from the january 1st to september 30th, but the money was transnferd every three months. If someone can give me an idea of how to do it it would be great. Thanks!

[@osamaaljaafari.6645]

Great job our virtuous teacher, I want to do the same way but at today's level except on Friday can you explain to me how

[@Montreal_powerbi_connect]

Amazing, Thank you.

[@obelato]

BR - obrigado, você é incrível !

[@rrrraaaacccc80]

Excellent 💯👍

[@williamarthur4801]

Well that was a bit of a marathon, I am having a go at typing it all into the formula bar in one , but so far I've only got up to Insert.Range , If I finish it I'll let you know , but it won't be today .

[@vishaljhaveri7565]

Thank you. Nice explanation! :)

[@GoodlyChandeep]

Welcome, Vishal!

[@iamchristian1434]

Great video! One question: how do you indent your m code? Tab is not working for me, it keeps jumping to the next field instead of indenting the code...

[@kimberlynnoggle5568]

BLESS this saved me thank you!!!

[@yami7475]

Hi Goodly, this is amazing, do you have example on how to get all the months in between start and end date? Like 4/1/24 to 12/1/24, I need 5/1/24, 6/1/24, 7/1/24… until 12/1/24 dynamically. Any help is highly appreciated. Thank you Yami

[@uniQue_XL]

Thanks for the great video! I've looked for a way to genetate psyments dates. Meaning, if sale has 10 payments, and the first payment date is August 25/12/2021 (dd/mm/yyyy), I want to split into rows, which mean on every month on the 25th for 10 times. Considering the change of year of course. Can the fomula you made can change with such senario?

[@johnharker3478]

Thank you, good walk through.

[@gingermo]

what if I want the same list, with exception to one column? So I want the rows being created by the list to be almost exactly like the original one, with exception to one column?

[@fayasfasal9663]

i need to create the months in between in the list , for eg i have November - DECEMBER , HOW CAN I CREATE THE INBETWEEN MONTHS IN LISTS

[@yvonnesilva7716]

Hello, If I needed a formula done to calculate end dates for school class scheduling, may I seek your services?

[@usmantariq3634]

Thnx Chandeep for showing your solution. I relied for my solution partially on UI and then manual formulas but i like to learn M coding. How do i approach learning M, how did you know which functions to correctly use in ur solution as there are 100s of functions and how can one know what's suited for what situation. Was just starting to get grips with DAX & now got interested in this. Please guide on how to approach M. Thanks again

[@GoodlyChandeep]

1 Don't fear dabbling with M 2 It'll be quite erroneous and painful at start and then quite useful later. 3 Read the official M Documentation from MS 4 Go through the gallery of M functions. See their examples 5 Start by changing tiny bits of MCode in the formula bar.

[@bhavnachalise]

Can we use list without exploding the data. In my case data is increasing upto 16 lakhs. Also is there any option i can create a dynamic date table. Based on one event selection. For eg one event is selected from the slicer, i have to show statuses changed between start and end date. So i want a table which creates all the date based on start and end date after selection one event. This i want to do as client does not want a date slicer and data is huge so i have a formula which calculates numbers in between dates also but with a disconnect date table only.

[@incekarayusuf]

@googly This is a very nice solution which make my life easy.. What if we need to insert rows based on pattern. Such as, A1 Start Date B1 End Date C1 Pattern (Days of the week 1234567 represents all days, 357 represents wed,fri,sun)

[@rahulkumar-3059]

What if there are nulls in date column? How we should handle them? I'm getting different data by including and not including the nulls. Can you help

[@dirkstaszak4838]

Nice and informative as usual

[@evanyenor1766]

How would you do this if you were to flip it around and say you want the last day of each month, and for the last record in the list you want the end date of the period (if not the last day of the month) rather than the end of the month? I know to get the end date of each month in the list you would use Date.EndOfMonth instead of Date.StartOfMonth, but I'm can't figure out how to insert the period end date in place of the last day of the month

[@eleftheriakoniari3392]

@Evan Yenor did you find the answer to this problem? I have the same problem and I can't find anything. Thank you in advance!

[@Shalini_Kanojia]

Great Tutorial! Can we generate weeks also between start and end date in power query? please share the solution?

[@jayt64]

What about when there are the duplicate dates ranges. I get null when I create the list

[@srilaxmigrao2734]

Hi interesting video. However I have one query, if I have list of employees from attendance I need to find for how many consecutive days the employee has worked, do u know how we can do this

[@GoodlyChandeep]

You need to use Grouping in Power Query with an optional parameter of GroupKind.Local. See this blog - blog.crossjoin.co.uk/2014/01/03/aggregating-by-local-groups-in-power-query/

[@lavleenkaur2405]

i have a problem - i have patient dashboard sp with start date and end date. i made date parameters in desktop and they are working fine. but , after publishing the dataset , applying parameters in power bi service and refreshing it , changes do not reflect

[@Mmaheeshttoorggal]

Hi Chandeep, this what I was working on from long time and you eased it for me... I really thank you and appreciate for this... One small request, how we can increment that investment after some specific period Ex - If the total period is of 3 years and that investment will increase in every year by 5%...

[@GoodlyChandeep]

It's quite cumbersome using DAX. Please take a look at forecasting formula in India Covid Dashboard - www.goodly.co.in/corona-virus-india-state-wise-dashboard/

[@Mmaheeshttoorggal]

@@GoodlyChandeep Thank you 🙏🏼

[@michaelpica4977]

How could I edit this to include the month the end date is in? Thanks for the video!

[@harshpannu8338]

You are awesome!!

[@hangtrinh7207]

It is great . But lot of Fumula for function.. I don't know how to remember all the fumula ..Any suggest for that ? Thaks very much

[@jerometurmel4208]

Very good content !

[@sebastienjailly349]

Thank you so much.

[@AnupamSinhaLovesMusic]

You're awesome!!!

[@GoodlyChandeep]

Thanks Anupam!

[@sauravsinha6939]

There is one probelm as the End date if ends in the mid of month how reflect the last row as the exact end date . Please share the solution too

[@arlequin71]

Hi Chandeep, awesome video!.. i was looking for a solution like yours to apporach a request. Just wondering how to split the ammount in corresponding months (if the amont is considered for the whole period between start and end date). Thanks a lot in advance for your answer.

[@georgechalkiadakis7185]

you have to count the days for each month , then the days for the whole period and then you have to multiply by the ammount (days/wholeperioddays)*ammount !

[@jankad6839]

I need the same solution but I want to use time not date. Can you show how do it? :)

[@ShahidShaikh-bg5uy]

How can create Dynamic filter using M query in sql server

[@aryastark5886]

Hello 👋 maybe you could help me about other topic but related to date as well. Problem : For my End date, I want to get the end of the month (date format) of my previous month as of today end of month local now values. Would this be possible? Example; Today EOM: Feb 28, 2022 Goal Prev EOM: Jan 31, 2022 Hope you can help me resolve this one. Many thanks 😊

[@zxyzxyzyzyugfd]

thanks for the video. My issue is i have 2 columns, one with month number (1 to 12) and the second column with only years (2021, 2022). I want a third columns with full date : 31.01.2021. How to do it ?

[@GoodlyChandeep]

Create a new column #date(year column, month number column, 1) Then change this column to month end date

[@ronakjindal65]

Hi Can you please help me creating date range slicer in Power Bi, Below is my query: I need date slicer in report which has start date as current month start date and end date will be 6 month later. Example : Start Date - 1st July 2022 and End date - 31 December 2022

[@christinelowery2415]

Can you generate list of dates where measure is

[@jonathankemp6803]

Save me today, thank you!

[@gayatrilshinde1560]

Thank you so much !

[@ankita80486]

Hi, How can this be done for Date times. Generate 1st date of each month between 3/23/2022 3:25:12 PM and 7/8/2022 9:14:00 AM. So something like 3/23/2022 3:25:12 PM, 4/1/2022 12:00:00 AM , 5/1/2022 12:00:00 AM, 6/1/2022 12:00:00 AM, 7/1/2022 12:00:00 AM, 7/8/2022 9:14:00 AM

[@rashmipatil9418]

I want the solution for all days of a moth till End date irrespective of start date. for instance if the start date is 12th May 2019 and end date in 21st July 2019, I would like to see start from 1st of May and till 31st July 2019

[@singhalmonica]

Dear sir, pls help me to understand tht whn i create pivot table with data modeling relationship, it repeats the regions in all dates, but when i do the same without relationship it shows only belowing regions to tht date... Pls help me to understand

[@singhalmonica]

Dear sir, i found the answer. Thax for u r priceless teaching.

[@dhruvpanchal9615]

How can I get the difference between dates in the same column with multiple criteria(keys) using power query ?

[@GoodlyChandeep]

can't give you a solution unless I see some data :|

[@GJD987]

Wish I was at this level!

[@GoodlyChandeep]

Don't worry, everyone starts with a zero. Ditto for me :) Practice, you'll get there!

[@toirshad]

Great one

[@pengseya_accladp6978]

Can we Generate Dates between Start and End Date like in this Video In Looker Studio?

[@armandcompar1197]

What if the Start date was greater than the End date, How would you ignore those cases and just evaluate the ones that are correct?

[@AbhishekZagade]

thanks

[@utkarshagarwal3480]

How can i do the same task in excel?

[@Alireza-ih5vu]

Perfect

[@GoodlyChandeep]

💚

[@vishnurao2901]

How if we want to bring all the dates between 2 dates?

[@munawarhussain6816]

Hi, Chandeep I really want your help, my customer placed orders in advance and the delivery dates could be for the current year or maybe next year. Now I want to calculate the total amount that until today how much amount of order did I receive for the current year. (not for next year) can you please help with this typical formula I have two dates Order placement date and the Order Delivery date can you please please help me. Thanks a lot in advance

[@srinivaskandru5034]

First Curbal stole this solution asking help from Power BI forum (solved by Imke Feldmann) and did a video on this. And he in turn doing a video on the same topic. My request as an onlooker of all this is, when you are doing videos like this, just add something extra to call this your own video. Otherwise people watch same content and feel the same what I have felt, Otherwise I like your videos, thanks Chandeep bhai.