Forum Discussion
Passing a List Query to a Query Parameter
Hi Anonymous ,
I'm hoping I get it this time. The code below creates a list of 9 rows containing the list of quarter start for the recent 9 quarters relative to today's date. Before the final step, only those dates from the current and previous year are selected. This list is then further filtered in the final step such that if today's date is earlier than the first friday of the quarter, only the past quarters are returned.
let list = List.Reverse({0..7}), //count of quarters to be deducted, a total of 8 rows today = Date.From(DateTime.LocalNow()), currentyear = Date.Year(today), firstfridayquarter = //get the date of the first friday of the quarter let dates = List.Dates(Date.StartOfQuarter(today), 7, #duration (1,0,0,0)) //first seven days in List.Select(dates, each Date.DayOfWeekName(_) = "Friday"){0}, //select Friday and return as text firstfridaytotoday = Duration.Days(firstfridayquarter - today), startofmonth = Date.StartOfMonth(today), //start of quarter of today's date quarters = List.Transform(list, each Date.AddQuarters(startofmonth, -_)), selectquarters = List.Select(quarters, each Date.Year(_) >= currentyear - 1 ) //select quarters from last year until current in if today >= firstfridayquarter then selectquarters else List.FirstN(selectquarters, List.Count(selectquarters) - 1)
Hi danextian ,
Just to add, I think what would help is having a list query which will only store the relevant quarters dynamically.
Right now, my list query has all the Dates for each month from 2003 till 2020.
Please correct me if I am wrong.
Thanks,
Vishy
Hi Anonymous ,
A more detailed description right fromt he start would have helped more :). A few more questions:
- How to know if a month has closed?
- Do you follow calendar or fiscal year (when does the fiscal year end)?
Hi danextian ,
Thanks for your prompt replies and yes I should have detailed it out better before.
Please refer to my response besides the question.
- How to know if a month has closed? - Month close happens when the database process runs which is usually the first Friday of the month. But the report will be generated only at the quarter ends. So once the monthly process runs for Mar, Jun, Sep and Dec on the first Fri of those months, the Date column will be loaded with those latest dates as applicable.
- Do you follow calendar or fiscal year (when does the fiscal year end)? - Calendar year is followed. At the end of each quarter month close i.e. Mar, Jun, Sep and Dec (on the first Fri), the report will be generated. So the current year will always be the calendar year and the previous year will be the year prior to the current calendar year.
Hope this clarifies.
Thanks,
Vishy
Hi Anonymous ,
I'm hoping I get it this time. The code below creates a list of 9 rows containing the list of quarter start for the recent 9 quarters relative to today's date. Before the final step, only those dates from the current and previous year are selected. This list is then further filtered in the final step such that if today's date is earlier than the first friday of the quarter, only the past quarters are returned.
let list = List.Reverse({0..7}), //count of quarters to be deducted, a total of 8 rows today = Date.From(DateTime.LocalNow()), currentyear = Date.Year(today), firstfridayquarter = //get the date of the first friday of the quarter let dates = List.Dates(Date.StartOfQuarter(today), 7, #duration (1,0,0,0)) //first seven days in List.Select(dates, each Date.DayOfWeekName(_) = "Friday"){0}, //select Friday and return as text firstfridaytotoday = Duration.Days(firstfridayquarter - today), startofmonth = Date.StartOfMonth(today), //start of quarter of today's date quarters = List.Transform(list, each Date.AddQuarters(startofmonth, -_)), selectquarters = List.Select(quarters, each Date.Year(_) >= currentyear - 1 ) //select quarters from last year until current in if today >= firstfridayquarter then selectquarters else List.FirstN(selectquarters, List.Count(selectquarters) - 1)