Solved: user parameters for import query report

Susan513

I have a report date field - that is set by me based on the month's reporting date, typically end of the month. This is used to calculate days between a task start date and this report date.

I would like to allow users of the report to change this date. So for example, if they wanted to see report data as of 6/16/2025 they could pick this date instead of my default 6/30/2025.

Is it possible to have this option via a parameter show before the report loads? I am not using to slice data - but to actually use for calculations of the data as it loads. I am using import query. I see ways to have a parameter in the report to slice data, but this is not exactly what i am trying to do.

If not, is there a quick way I can incorporate in the report itself, where the full report doesn't need refreshed with every date change? I have the report date in excel - and I know I can update the date here, then refresh - but trying to find a way to manage in power bi directly and avoid a refresh. The date is critical in a custom column in power quesry for calculations.

MarkLaf

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.

View solution in original post

MarkLaf

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.

Susan513

This is great thank you. The user is typically putting in today's date - but I have a requirement that the date be the month end date the last week of the month. I like your approach, this makes total sense. Currently I have just implemented a power automate to update to todays date except for the specific days I want it to be the end of month. I will take a look at your solution, as I think this gives more flexibility in case they want to see a past date. My current solution is always today. I can add a field with the date, default to my date, then they can pick what they like. Then my calculation gets the date from the field whether their date or mine and we are golden. You are correct, I am just using for caclulation and not trying to change anything in the dataset. I just have to make sure the calculation updates when they change the date - which I am guessing would be a measure on the front, instead of in a column like I have currently. Thanks for the solution!