Forum Discussion

Susan513's avatar
Susan513
Helper II
1 year ago
Solved

user parameters for import query report

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 wo...
  • MarkLaf's avatar
    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

    IDStartEnd
    11/1/20251/20/2025
    21/25/20253/1/2025
    32/10/2025null
    43/5/2025null
    54/25/2025null

     

    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.