How to remove or exclude rows from a table using date slicer selection?

Question

As per the below scenario,I need to exclude all the rows in the table Sales with columns Client,sales,salesdate and region which fall outside the selected maximum and minimum date range of the date slider.

Could anyone please suggest a DAX , I will share my DAX query which is not working.I tried to search official guides in PowerBI for selectedValues() to handle this date filter condition but nothing seems to be working.

I created a Calendar table with distinct values of the dates as present in the Sales Dates column of the Sales table. There is no relationship between these two tables. I used the below DAX measures to handle the filtering criteria for excluding the rows:- Below measures are meant to find maximum and minimum dates in the selection slider

MaxDate=MAX(DateDimention[Dates])
MinDate=MIN(DateDimention[Dates])

Below ExcludedMeasure is meant to set 1 flag to the values other than selected values which are selected for both min and max dates.

I dragged and dropped it in the visual filter of my table Sales and set it to dont show.But it seems to be not working. Please suggest how can I fix it.

ExcludeMeasure = IF (SELECTEDVALUE(Calendar,Calendar[MinDate],0,1),
                              IF (SELECTEDVALUE(Calendar,Calendar[MaxDate],0,1)

My Present Visualization and independent Calendar table to be used in the filter slider:- Present visualization Independent Calendar Table

Input data source [Excel]

Client  Sales     SalesDates   Region
    A       1000    1.1.2000    USA
    A       100     1.2.2000    USA
    A       200     4.3.2000    USA
    B       110     4.3.2000    Europe
    B       1000    5.4.2000    Europe
    B       200     6.8.2001    Europe
    C       1100    7.9.2001    Asia
    C       2000    8.12.2001   Asia
    D        100    1.2.2002    Australia
    D       1300    6.3.2002    Australia
    E        100    7.5.2002    Africa

Expected Results : If I select Minimum slider date as 01.04.2001 and maximum slider date as 1.09.2001 in my[Dates] filter visualization, then it should return the below results after excluding '5.4.2001' and '6.8.2001':-

Client  Sales   SalesDates  Region

A       1000    1.1.2000    USA
A        100    1.2.2000    USA
A        200    4.3.2000    USA
B        110    4.3.2000    Europe
C        1100   7.9.2001    Asia
C        2000   8.12.2001   Asia
D        100    1.2.2002    Australia
D        1300   6.3.2002    Australia
E        100    7.5.2002    Africa

Kind regards Sameer

And why are you not using relationships between sales and date tables? This is the simplest way to solve your problem. — W.B.
Could you please give me an example based on my above data as I am not able to proceed with the said approach by you. — sameer

Joao Leal Joao Leal · Accepted Answer · 2021-02-18T23:38:19

A relationship between the date table and the sales table would be ideal, as when you filter the date from the date table, the filter would exclude the records from the sale tables.

If you can't have a date table with the relationship, your exclude measure needs to be something like this:

Exclude = 
CALCULATE(COUNT(Sales[Id]), 
          Sales[SalesDates] >= [MinDate] && 
          Sales[SalesDate] <= [MaxDate]) > 0

How to remove or exclude rows from a table using date slicer selection?

1 Answers