Passing a List Query to a Query Parameter

[vishy86]

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 on the same Date column. The condition is that since the report is only generated on a quarterly basis, the user only wants data loaded for the current year quarters and previous year quarters. Eg: If data is loaded today, the load would only be for Date: 01-Mar-20, 01-Dec-19, 01-Sep-19, 01-Jun-19 and 01-Mar-19.

I tried creating a list query for Date with unique values and linked it to my Query Parameter. But the only concern with that is I have to manually update the List Query periodically which kind of bolis down to hard coding which isn't desired.

Is there any way to pass the query to a parameter with the query dynamically showing the current year quarters and previous year quarters only with respect to the current year and month.

Thanks,

Vishy

[danextian]

Hi @vishy86 

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, -_)) 

[vishy86]

Hi @danextian ,

The database process for Date runs every month close which is the first Friday of the month. However, the report will run every quarter after the month close is processed.

I would like to explain the requirement with the help of scenarios.

Scenario 1: Today is 6th Mar which is the first Fri when the database process runs which implies that the month close process is still not completed.

In this scenario, the report will show the 4 quarters of 2019 i.e. Dec, Sep, Jun and Mar 2019 and the 4 quarters of 2018 i.e. Dec, Sep, Jun and Mar 2018.

Scenario 2: Assume today is 10th Mar 2020 i.e. the month close process is now completed. 

In this scenario, the report will show the current quarter of 2020 i.e. Mar 2020 and the 4 quarters of 2019 i.e. Dec, Sep, Jun and Mar 2019.

Scenario 3: Assume today is 10th Jun 2020 i.e. the month close process for Jun 2020 is completed.

In this scenario, the report will show the current quarters of 2020 i.e. Jun and Mar 2020 and the 4 quarters of 2019 i.e. Dec, Sep, Jun and Mar 2019.

The list query needs to dymanically pass only the desired quarters to the parameter for the user to select.

Hope this clarifies. Do let me know if there are additional queries.

Thanks,

Vishy

[vishy86]

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

[danextian]

Hi @vishy86 ,

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

[vishy86]

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

[danextian · Accepted solution]

Hi @vishy86 ,

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)