Forum Discussion
Dynamic parameter
Is there a way to dynamically change a parameter value?
I have a parameter "MaxDate" which needs to have yesterday's date.
I have created a table with a single cell that contains yesterday's date that updates everyday, if that helps.
But when i add this to the query of parameter, it doesnt automatically update, i have to manually update the current value of the parameter every day.
Is there any solution to this?
I'm not stuck in stone to use this table, if there is any other way to achieve this dynamic update of parameter, I'm open to that as well (like using the advanced editor or something).
Thanks in advance!
Instead of using a paramter, I just created a table with a single cell that calculates the yesterday's date using the formula: Date.AddDays(Date.From(DateTime.FixedLocalNow()),-1)
I then add this column to my original table, where I was initially using the parameter.
Now that column updates as the above created table updates (everytime i refresh)
3 Replies
Yes, you can use Power Query to dynamically update the parameter value. Here's how you can do it:
In Power Query, create a new query that retrieves yesterday's date. You can do this by connecting to your data source and filtering for the date that is one day before today's date.
Add a step that selects the date value from the query. You can do this by selecting the column with yesterday's date, right-clicking on the column, and selecting "Use First Row as Headers".
Rename the column to "MaxDate" to match the parameter name.
Load the query as a connection only, by selecting "Connection Only" from the Load Options.
Go to the parameter settings and change the Default Value to the following expression: = DateTime.LocalNow() - #duration(1,0,0,0)
Save and refresh your report. The parameter should now have yesterday's date as the default value.
This method will ensure that the parameter value is always up to date and does not require manual updates.
Instead of using a paramter, I just created a table with a single cell that calculates the yesterday's date using the formula: Date.AddDays(Date.From(DateTime.FixedLocalNow()),-1)
I then add this column to my original table, where I was initially using the parameter.
Now that column updates as the above created table updates (everytime i refresh)
