Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Passing a List Query to a Query Parameter

Hi,   I have a Date column. It contains montly dates from 01-Dec-03, 01-Jan-04, 01-Feb-04 and so on till current 01-Feb-20.   I want to pass this Date column as a List Query to my Query Parameter...
Need Help
Show and Tell
Tips and Tricks
  • danextian's avatar
    danextian
    6 years ago

    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)

     

danextian's avatar
danextian
Super User
6 years ago

Hi Anonymous 

Do you need to a list five quarters including today's quarter?

The query below returns a list of date based on the start of month of today's date and then goes a number of quarters backwards. If you want the end point to be the start date of current quarter, just replace StartOfMonth  with StartOfQuarter.

 

= let 
list = List.Reverse({0..4}), //count of quarters to be deducted
startofmonth = Date.StartOfMonth(Date.From(DateTime.LocalNow())) //start of quarter of today's date
in 
List.Transform(list, each Date.AddQuarters(startofmonth, -_))