Dynamic filtering of rows using a parameter

[Klaus]

Hello,

I have a table with two attributes: [order_dates] and [sales]. I want to filter rows fitting the following condition:

number of days between MAX(Order_Date) and order_date >= X.

X should be selected by the user through a parameter. The parameter must be visible on the report page.

That means, a query parameter cannot be used

I created a parameter table, which holds all possible values for X.

Then I created a calculated column as follows

Flag =
IF (
    DATEDIFF ( 'data'[order_date]; [Latest_Order_Date]; DAY )
        >= SELECTEDVALUE ( 'ParameterTable'[Value] );
    "INCLUDE";
    "EXCLUDE"
)

Latest_Order_Date is defined as follows:

Latest_Order_Date =
MAXX (
    ALL ( 'data' );
    'data'[order_date]
)

Unfortunately this does not work. Whatever value I select in the parameter table has absolutely no effect. What I am doing wrong?

Thanks a lot in advance,

Klaus

[BILASolution · Accepted solution]

Hi @Klaus

You can try this...

1. Create a new calculated column (days)

days = DATEDIFF(Sales[OrderDate];MAX(Sales[OrderDate]);DAY) 

2. Finally, Use it as an slicer. Look the picture below.

NOTE: The right side of the slicer must be in the right limit

I hope this helps

Regards

[BILASolution · Accepted solution]

Hi @Klaus

You can try this...

1. Create a new calculated column (days)

days = DATEDIFF(Sales[OrderDate];MAX(Sales[OrderDate]);DAY) 

2. Finally, Use it as an slicer. Look the picture below.

NOTE: The right side of the slicer must be in the right limit

I hope this helps

Regards

[Klaus · Accepted solution]

Thank you a lot!

You are right. In this case a parameter is absolutely not necessary.

Your suggestion works perfectly.

Kindest regards