Forum Discussion
Dynamic parameter
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)
Solution SageYes, 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.
Thanks for your response.
I have created the MaxDate column with yesterday's date in the row. But adding the above expression to parameter raises the following error:Can you help me sort this please?