Forum Discussion
user parameters for import query report
- 1 year ago
It sounds like your query against the data source is not impacted by the reporting date in question, rather you are just looking to create a dynamic age measure where the start date is based on your data and the end date is based on a date selected by the user?
This is relatively straightforward, just make a new table of the dates you want the user to select from and then reference that in your age measure.
Here is an example with simple data and model.
Table
ID Start End 1 1/1/2025 1/20/2025 2 1/25/2025 3/1/2025 3 2/10/2025 null 4 3/5/2025 null 5 4/25/2025 null Here is a simple model where we already have a Dates table for time intelligence, etc.
What we want to do is now create a new table that is unrelated from everything else in your model.
As an example, let's say we want people to select from the back half of 2025:
Date Select = CALENDAR( DATE( 2025, 7, 1 ), DATE( 2025, 12, 31 ) )New model:
Now we can use this in a dynamic age measure, which will change based on the 'Date Select'[Date] selected in a slicer.
Dynamic Age = DATEDIFF( MIN( 'Table'[Start] ), MIN( 'Date Select'[Date] ), DAY )Or, here is an alternative where the age is based on the actual End Date and only uses the user-selected end date for open items (i.e. End Date is blank).
Dynamic Age_Open Only = DATEDIFF( MIN( 'Table'[Start] ), COALESCE( MIN( 'Table'[End] ), MIN( 'Date Select'[Date] ) ), DAY )Here is a quick snip of this in action. We select 'Date Select'[Date] in the slicer, which impacts the age measures we defined above. The ages are based on MIN( 'Date Select'[Date] ), which is the equivalent of the Earliest Date card.
Learn about power query parameters here
https://learn.microsoft.com/en-us/power-query/power-query-query-parameters
https://www.youtube.com/watch?v=twBUmqVOGgg
Learn about report parameters here
https://www.youtube.com/watch?app=desktop&v=wrKEyhSUaxQ
Please click thumbs up and accept solution
Thank you
Thank you for the links - none seem to help with a solution to my problem.