Forum Discussion
Community Championpassing a calculated date parameter to multiple imports
what is the best way to calculate a date 2 years back, from the beginning of the current month and then pass that to a filter in all the tables you importing into a model.
Ive tried this but it doesn't seem to be coming together..
Ive created a calculated table in power query by placing in this in a blank query. It comes up with the correct date.
= Date.AddYears(Date.StartOfMonth(DateTime.Date(DateTime.LocalNow())),-2)
this returns athe correct value, but can't figure out how to pass it to a parameter, at its not showing as an available query when i create a new parameter, what is the best way to do this?
thanks
Hi vanessafvg
What you could potentially do is the following.
In the Query Editor where you have created your Date going back 2 years, Convert this to a List.
Then create a new Parameter, when it asks for the Suggested Values, change this in the Drop Down to Query.
This will then give you an option for which Query to use.
You can now select the List that you converted above.
Now that you have it set as a Parameter, you can simply use this Parameter in your filters.
There is another way to do this, if it does not work, which is to manually create it in the Advanced Editor, but is a bit more complex. So if you can try the above suggestion first?
9 Replies
- vanessafvg
i would really aprpeciate a response on this if anyone knows :) as i am totally stuck on how to make this dynamic
- GilbertQ
Super User
Hi vanessafvg
What you could potentially do is the following.
In the Query Editor where you have created your Date going back 2 years, Convert this to a List.
Then create a new Parameter, when it asks for the Suggested Values, change this in the Drop Down to Query.
This will then give you an option for which Query to use.
You can now select the List that you converted above.
Now that you have it set as a Parameter, you can simply use this Parameter in your filters.
There is another way to do this, if it does not work, which is to manually create it in the Advanced Editor, but is a bit more complex. So if you can try the above suggestion first?
- vanessafvg
aha i think it might work with the list! i only just noticed that you convert to list or table. , i just hope it stays dynamic, thanks
- dearwatson
Continued Contributor
Hmm.. this can be tricky depending on the data source:
if its in SQL DB its easy.. is it a SQL source?
Cheers
Greg
- vanessafvg
unfortunately the source is in csv format
