cancel
Showing results for
Did you mean:

## Power Query - Generate List of Dates with intervals of Months, Quarters and Years

Use Case - There are situations where we need to generate a list of dates. Let's say start date is 3-Jul-22 and we want to generate 100 dates in the series. Hence the series will contain dates starting 3-Jul-22 till 10-Oct-22. Following formula can be used to generate this list

``= List.Dates(#date(2022,7,3),100,#duration(1,0,0,0))``

If you want to generate dates at a gap of 1 week i.e. 7 days, then we just change #duration(1,0,0,0) to #duration(7,0,0,0).

``= List.Dates(#date(2022,7,3),100,#duration(7,0,0,0))``

But #duration's highest argument supports days only. It doesn't support months, quarters and years. For week, we changed the day parameter to 7.

So, how to generate list of dates when interval is months, quarters and years

Solution - We can make use of List.Generate for this purpose. Following formulas can be used to generate these lists

For monthly interval

``= List.Generate(()=>[x=#date(2022,7,3),i=0], each [i]<100, each [i=[i]+1,x=Date.AddMonths([x],1)], each [x])``

For quarterly interval

``= List.Generate(()=>[x=#date(2022,7,3),i=0], each [i]<100, each [i=[i]+1,x=Date.AddQuarters([x],1)], each [x])``

For yearly interval

``= List.Generate(()=>[x=#date(2022,7,3),i=0], each [i]<100, each [i=[i]+1,x=Date.AddYears([x],1)], each [x])``

Bonus Formulas - Sometimes, you won't be given how many numbers to generate i.e. value of 100 in above formulas will not be given. But instead, you will be given the Start Date and End Date only and you need to generate the list.

Let's assume Start Date is 18-Mar-2022 and End Date is 24-Jun-2025. Then formulas will be following

For daily interval

``= List.Dates(#date(2022,3,18),Duration.Days(#date(2025,6,24)-#date(2022,3,18))+1,#duration(1,0,0,0))``

For weekly interval

``= List.Generate(()=>[x=#date(2022,3,18)], each [x]<=#date(2025,6,24), each [x=Date.AddWeeks([x],1)], each [x])``

For monthly interval

``= List.Generate(()=>[x=#date(2022,3,18)], each [x]<=#date(2025,6,24), each [x=Date.AddMonths([x],1)], each [x])``

For quarterly interval

``= List.Generate(()=>[x=#date(2022,3,18)], each [x]<=#date(2025,6,24), each [x=Date.AddQuarters([x],1)], each [x])``

For yearly interval

``= List.Generate(()=>[x=#date(2022,3,18)], each [x]<=#date(2025,6,24), each [x=Date.AddYears([x],1)], each [x])``

Download the sample file named List Of Dates_v3.pbix containing above examples.

--- End of Article ---

Hello,

I have created a date table with weekly intervals based on what  posted above. The start date was applied as "2008-04-07" end date as "2023-12-31", but the result returns a date table with end date of "2118-05-23". What's the problem? The code used is as follows. "=List.Dates(#date(2008,4,7),Duration.Days(#date(2023,12,31)-#date(2008,4,7))+1,#duration(7,0,0,0))"

I am so sorry, I need to modify my formulas which I will today for this kind of scenario. You will need to use either of the following formula

``````= List.Dates(#date(2008,4,7),Number.RoundDown(Duration.Days(#date(2023,12,31)-#date(2008,4,7))/7,0)+1,#duration(7,0,0,0))

= List.Generate(()=>[x=#date(2008,4,7)], each [x]<=#date(2023,12,31), each [x=Date.AddWeeks([x],1)], each [x])``````

This will give 25-Dec-2023 as the last date.

I changed the formula above so that the end date is specified by today. Thank you

= List.Generate(()=>[x=#date(2008,4,7)], each [x]<=DateTime.Date(DateTime.LocalNow()), each [x=Date.AddWeeks([x],1)], each [x])

Hello,

``= List.Generate(()=>[x=#date(2022,7,3),i=0], each [i]<200, each [i=[i]+1,x=Date.AddMonths([x],1)], each [x])``

I am using the above monthly interval formula to generate 200 rows. But, end date is dynamic based on current date.
Suppose, this is Oct 2022 which is same as FY 2023-07 (Fiscal Year) and the end date should be restricted to the current fiscal month, which is 2023-07.
Can you think of anything?

You needed to use this formula

``= List.Generate(()=>[x=#date(2022,3,18)], each [x]<=#date(2025,6,24), each [x=Date.AddMonths([x],1)], each [x])``

For Current month what should come in place  #date(2025,6,24)?

Hello @Vijay_A_Verma , I am aiming to get the list of quarter dates based on start date and number of quarters between the start and end date using the formula below:

List.Generate(()=>[x=[Start_Date],i=0], each [i]< [3_Months], each [i=[i]+1,x=Date.AddQuarters([x],1)], each [x])

However, I am getting an error:

Expression.Error: The field '3_Months' of the record wasn't found.
Details:
x=16/12/2021
i=0

Is there an alternative to make the list of dates of quarters dynamic based on input data?

Hello,
What I need is to create a list with the first and the last date. From a date up to 1 or 2 years in the future.

01/01/2022
31/01/2022
01/02/2022
28/02/2022
01/03/2022
31/03/2022

I still can't find the right key. Any help??

Tks!!!!

Please use this code (replace dates as per your need)

``= List.Generate(()=>[x=#date(2022,1,1)], each [x]<=#date(2022,12,31), each [x=if [x]=Date.StartOfMonth([x]) then Date.EndOfMonth([x]) else Date.StartOfMonth(Date.AddMonths([x],1))], each [x])``

[3_Months] = Means that it is a column name

3_Months = This is a variable name

Your code is not able to find [3_Months], it means that there is no such column.

Top Kudoed Posts
Latest Articles
Archives