Table visual requires dates prior to and after those selected in date slicer NOT those selected

[REWright]

Am trying to identify a way to reverse the logic of a date slicer. Rather than the table visualization returning the LastOrderDate for those customers within the date range selected, the request is to return the records that are prior to and after the date range selected.  The key fields are CustomerAccountID, CreatedDateTime (proxy for order date).  Getting a count of those Customers whose LastOrderDate outside the selected date range will be step 2.

Example: Customer ABC has a LastOrderDate of February 25, 2025.  The user selects the date range of 1/1/2025 - 2/28/2025.  In this scenario, Customer ABC would not be included in the table visualization.

However, if the user changes the date range slicer to 1/1/2025 - 2/14/2025, then Customer ABC will appear in the table visualization as their LastOrderDate (2/25/25) occurs after the End Date of the slicer (2/14/25).

Similarly, if Customer XYZ has a LastOrderDate of 12/16/2024 and the Date Range Slicer is set to 12/1/2024 - 12/31/2024, then Customer XYZ will not appear in the table visualization.  If the date is changes to 12/21/2024 - 12/31/2024, then Customer XYZ will appear in the table visualization as their LastOrderDate precedes the Start Date of the slicer.

[REWright]

Ended up creating an alternate approach to this scenario.  Identified the count of Total # of Customers, count of Customers whose Last Order Date was in the selected date range, then created a measure to calculate the difference of the two (Total - In Date Range).  Not elegant, but a series of other explorations didn't render any successful results to do this straight forward.  Thanks for any/all suggestions that were made in an effort to assist.

[Thejeswar]

Hi @REWright ,

In this case, I would use the approach of having two disconnected calendar tables and restricting the data.

1. Have two slicers One with StartDate and One with EndDate. The Date should be from two independent tables that do not filter each other. The Below is the screenshot. Table is my main table, Calendar1 is my startdate, Calendar2 is my end date. There is no relationship between these tables in this approach

2. Write a measure such that the measure returns 1 if the conditions are met. The Below is the measure

ValidDates = IF( OR(MAX('Table'[Start Date]) < SELECTEDVALUE(Calendar1[Date]),MAX('Table'[Start Date]) > SELECTEDVALUE(Calendar2[Date])), 1, 0)

3. Add this ValidDates measure as a visual filter and filter it to display only 1s as shown below

As you see from the above screenshot, the table display all dates before August 7th and all dates after August 16th, 2024.

Attached the pbix here for reference

If this solves your question, Mark it as Solution and Appreciate with a Kudo !

Regards,

[REWright]

Thank you for this alternative approach. The wrinkle comes in my data with the LastOrderDate being a measure.  The table visual contains CustomerAccountID and LastOrderDate.  Theoretically the slicer(s) should then define the content excluded by LastOrderDate (before/after). When I modify your 'ValidDates' measure it returns the error "Column 'LastOrderDate' in table 'vw_gtlf_Sales' cannot be found or may not be used in this expression.  

ValidDates = IF( OR(MAX('vw_gtlf_Sales'[LastOrderDate]) < SELECTEDVALUE('dates'[Date]),MAX('vw_gtlf_Sales'[LastOrderDate]) > SELECTEDVALUE('Calendar'[Date])), 1, 0)

[Deku]

This measure calculated the last order, ignoring the slicer and returns the date if the last order date is outside of the filtered date range, otherwise returns blank.

Add this to a table with customer and filter the measure where is not blank

Last order =

Var LastOrder=

Calculate(

Max( table[CreatedDateTime] ),

Removefilters(date)

)

Return

If(

not( lastOrder in values(date)),

LastOrder

)

[REWright]

Thank you for providing a proposed approach. In your code, what is the (date) reference addressing?  Simply creating a measure with this code and replacing the table name for 'table' renders the following error: Failed to resolve name 'date'. It is not a valid table, variable, or function name.

[Deku]

date is a date dimension table

something like

dates = 
ADDCOLUMNS(
    CALENDAR( MIN( 'Table'[date] ), MAX( 'Table'[date] ) )
    ,"Year", YEAR( [Date] )
    ,"Year Month", EOMONTH( [Date], 0 )
)

[REWright]

Thank you, again, for your efforts to assist. After creating the 'dates' table, I joined to the main vw_Sales table on CreatedDateTime.  Added a table visual to the pane, included CustomerAccountID and the 'Last order' measure.  Created slicer, added 'Date' from the 'dates' table.  The Last order filters on this visual is set to 'is not blank'.  The table still includes dates in the selected date range.