Programmatic params in M to set dates

Behold this M code!

// func get_start_date()



let

    Source = (lookback_period as number) => 

    let

        Source          = Date.AddMonths(    DateTime.LocalNow(), -lookback_period),

        start_datetime  = Date.StartOfMonth((Source)),

        start_date		= Date.From(		start_datetime)

    in

        start_date

in

    Source

where the fn param, lookback_period, receives its value from any number of predefined parameters. Let me explain:

Suppose you want to parameterize a start date so that from the Service you could control how much data will be loaded on the next refresh from an on-prem DB. Right now, the only way to do so with a Date type parameter, is to manually hard code a literal date in the dialog where you manage parameters.

Here is a better way to do this, which does not work at the moment.

1_ define the function above.

2_ let's say you need 2 different lookback period; define 2 params of type Number (ideally integer, but only decimal type is available, so be it!): lookback_0 and lookback_1.

3_ assign 2 different numerical values to each parameters that represent the number of months you want to go back in time, ie localtime - lookback_period = start date from when to load the data.

4_ let's say you have 2 different queries in your query staging folder. call the get_start_date() function in each query, so get_start_date( lookback_0 ) in query_0 and get_start_date( lookback_1 ) in query_1.

5_ publish the report and go to the Service.

6_ go to the Datasets Settings and open the Parameters section.

At this point, Power BI Service will show you the 2 lookback params and let you set new values BUT will not use these new values when the get_start_date() is referenced in the queries M code. If you refresh the report, the param values used in the get_start_date() function call will be those set during the desktop editing session at publish time.

Would be nice to have the mechanism above available. Also, when opening the Manage params dialog in Power Query and selecting Query in the Suggested Values drop-down list, none of the available queries appear and so well... this needs to be fixed (Power BI Desktop April version).