Filter a table by date using DAX - again

[Anonymous]

Greetings all,

Sorry for being stupid, I'm still very new to DAX. Back in 2019 in this community, a question was asked about filtering dates. I think I'm trying to do the same thing, but I'm failing to get the needed result.

Here is the 2019 post: Filter a table by date using DAX 

I have a generic date table that I use in various PBI reports (starts in 2019, ends in 2025). I link it to dates fields in various data tables, drop the [date] field into a time slicer and build out my visuals from there. I'm trying to find a good way to limit that generic date table to a date range relevant to whatever report I'm building. For example, we rolled out a new tracking system the day after Christmas this past year (2021). I'd like to constrain the date range for a report that visualizes data from the new system from Dec 26, 2021 through TODAY(). I want my time slicer visual to only offer that range to my report users.

I don't think I care whether I build a new virtual date table and apply dates from the new table to my time slicer, or filter my current date table. Whatever works is fine!

I think I followed the solution provided by @jdbuchanan71 , but it threw an error back at me. (Apologies again, if I weren't so new to DAX, the error message would probably mean something to me and I wouldn't be here!)

Date Range =

CALCULATETABLE(

'date table',

DATESBETWEEN ( 'date table'[date], DATE(2021,12,26), TODAY())

)

Seems like this should have been easy, but I'm failing. Thanks for any help!

[Anonymous]

@amitchandak and @johnt75 , thank you both! I like both your answers. @amitchandak , yes, as a new user of DAX, I do still struggle with Measures versus Tables. And yes, copying my code from the measure to a newly created table made a new range-constrained table just like I wanted. @johnt75 , that is clever! I had been fighting with a filter on the slicer visual. I tried Advanced Filtering and selected "is on or after" and put in the Dec 26, 2021 date but when I tried to use the "And" option, there was no obvious way to specify is "is on or before" TODAY. But your idea creates a solution to that problem! Nice.

Now I need to decide who to give the "Accept as Solution" award... Dang, both ideas work and make sense.... @amitchandak , I'm giving it to you since your's was yet another step in my trying to sort out the proper application of measures versus tables. But @johnt75 , I'm probably actually going to use your solution in my current report. Wish I could give it to both of you!

[johnt75]

Add a new column to your date table like

Today or Before = 'Date'[Date] <= TODAY()

then add 2 filters onto the necessary visuals. Firstly, add the date field as a filter and choose Advanced Filtering -> is on or after and put in December 26, 2021.

Secondly, add the new column you created as a filter and only show values where Today or Before is true.

[amitchandak · Accepted solution]

@Anonymous the above code is for table not meaure

so either create a table

to create a measure like

countorws(CALCULATETABLE(
'date table',
DATESBETWEEN ( 'date table'[date], DATE(2021,12,26), TODAY())
))