Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Vijay_A_Verma

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 ---

Comments

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!!!!

@PabloSM 

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])

 

@ruariah 

[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.

Hello @Vijay_A_Verma 

I would like to generate a list of months within a Power Query table via a custom column to expand, based on a start date and end date fields.

I replaced the hard coded end and start dates by the column names of my tables :

 

Table.AddColumn(#"Extracted Date", "list", each List.Generate(()=>[x=[start]], each [x]<=[end], each [x=Date.AddMonths([x],1)], each [x]))

 

2023-02-22 18_44_14-Tableau1 - Éditeur Power Query.jpg

Then, I get the following error message when I expand the custom column (called "list" in my case) :

Expression.Error: Sorry... We couldn't find the "end" field of the record. Details :    x=01/01/2020

Can you help me please ?

Thanks

Hello @Vijay_A_Verma,

Please, is it possible to pull a Start date from a column array in a table using the List date function, without actually going to manually check for it, and if so, how do you apply it in the power query

Hi @Vijay_A_Verma ,

I used your example Bonus Formulas for yearly interval and replaced the dates with dynamic column names. When expanding the list in the column I receive the following error message: Expression.Error: The field 'Ende' of the record wasn't found.
Details:
x=14.06.2023.

Formular is the following: List.Generate(()=>[x=[Beginn]], each [x]<=[Ende], each [x=Date.AddYears([x],1)], each [x])

Is there anything missing?

Regards Renata

 

It means that there is no column naamed Ende in your table. Please see what is the correct name.