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

[Vijay_A_Verma]

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

List Of Dates_v3.pbix