Creating a dynamic parameter - possibilities? Current date to week number

[Martinj9]

Hi, 

I was wondering if someone had any experiance with creating dynamic parameters? 
Currently trying to create a parameter that updates itself based on current date, for today I would for instance want the refresh to automaticly lookup date 08.10.2019, and retrieve the correct week format "[ZCBWEEKL].[201941]". 

I have created a date table that is linking each date to a certain week with the correct format, but I am unable to create the lookup from the Power Query editor where I currently have {Cube.ApplyParameter, "[!V000004]", "[ZCBWEEKL].[201941]]"}}, but want it to lookup correct date through the date table. 

Would be very greatful if someone has a solution for this! 
I currently have scheduled an automatic refresh, but will always need to change the parameter manually. 

[AnkitBI · Accepted solution]

Hi - You can try something like below. This will create a Query with single value i.e. YYYYWW. You can then refer this query inside your Main Query for filter.

let
    Source = #table({"CurrDate"},{{DateTime.LocalNow()}}),
    Custom1 = Table.AddColumn(Source,"Week",each Text.From(Date.Year([CurrDate])) & Text.From(Date.WeekOfYear([CurrDate]))),
    Custom2 = Custom1{0}[Week]
in
    Custom2

Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.

[Martinj9]

Hi Ankit, 

Excellent. This worked perfectly. Thanks you! 

[Anonymous]

I use functions all the time to create dynamic parameters for my queries.  Its pretty easy just do this:

Create a new Query, set enable load to false.  Give it a meaningful name, i normally start the name with "fn".

Inside your query, make the source line whatever you need to get the value you want.  You can use all the normal Power Query time intelligence features.

Now, in your Source line for your table, you can quote the fn query to get the dynamic result.

You can also combine this with Parameters in Power BI.  I use this to have a "Last X Months" and then set how long I want my query to go back by having the function rely on the Parameter value, it then calculates the "Start Date" and passes that to the Stored Procedure its calling in SQL.