Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Query parameters filter another column

hello everyone,

 

i have a transaction table which has transaction ID, transaction date, account date and some other fields.

what i need to do is create two query parameter on transaction date  [start date and end date]. When the user gives a start date and end date, the report should display transaction IDs only for the last account date of the months in the given range.

For example,

i have given start date as 21 Dec 2018

and end date as 3rd March 2019

 

the report should display transactions only for

31st Dec 2018

31st Jan 2019

28th Feb 2019

31st March 2019

 

is this possible? please help

 

thanks in advance

Need Help

1 Reply

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous 

    You may create a measure like below.Then use it in visual level filter.Attached sample file for your reference.

    Measure = 
VAR E_account_date =
    ENDOFMONTH ( Table1[account date] )
VAR E_start_date =
    ENDOFMONTH ( 'Start date'[Start Date] )
VAR E_end_date =
    ENDOFMONTH ( 'End date'[End Date] )
RETURN
    IF (
        MAX ( Table1[transaction date] ) >= MAX ( 'Start date'[Start Date] )
            && MAX ( Table1[transaction date] ) <= MAX ( 'End date'[End Date] ),
        IF (
            MAX ( Table1[account date] ) = E_account_date
                && MAX ( Table1[account date] ) >= E_start_date
                && MAX ( Table1[account date] ) <= E_end_date,
            1
        )
    )

    Regards,