cancel
Showing results for 
Search instead for 
Did you mean: 
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)?

Polls
What is your favorite Power BI feature release for November 2022?