Forum Discussion

Martinj9's avatar
Martinj9
Frequent Visitor
6 years ago
Solved

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

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 insta...
Need Help
Tips and Tricks
  • AnkitBI's avatar
    AnkitBI
    6 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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.