Filter a table by date using DAX

[Anonymous]

Hello,

How can I filter a table for a date field using DAX? I have a date range to filter.

The output would be a second table.

Thank you!

[v-eachen-msft]

Hi @Anonymous ,

You can set date range directly.

Table =
VAR DateStart =
    DATE ( "2018", "1", "5" )
VAR DateEnd =
    DATE ( "2018", "1", "15" )
RETURN
    CALCULATETABLE (
        TEST,
        FILTER ( TEST, TEST[Date] <= DateEnd && TEST[Date] >= DateStart )
    )

Or you can set a  start time and time interval to define your date range.

Table 2 =
VAR DateStart =
    DATE ( "2018", "1", "1" )
RETURN
    CALCULATETABLE ( TEST, DATESINPERIOD ( TEST[Date], DateStart, 15, DAY ) )

You can define the time interval by day, month, season, year. Here is the documentation for DATESINPERIOD() function.

https://docs.microsoft.com/en-us/dax/datesinperiod-function-dax

These two DAX queries return a same table. You can choose one of them.

Here is my test file, you can click the following link and download it.

https://wicren-my.sharepoint.com/:u:/g/personal/michael_wicren_onmicrosoft_com/ETVz0nkTJ3NMhRKnZJ6dRKkBeR8uUTIP0NtJdTnYyXqz0g?e=b28dSt

Best Regards,

Eads

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jdbuchanan71 · Accepted solution]

Hello @Anonymous 

Give this a try, you will just need to change the table and column names to match your data.

Filtered Sales = 
CALCULATETABLE(
    Sales,
    DATESBETWEEN ( Sales[Sales Date], DATE(2019,1,15), DATE(2019,2,5) )
)

This creates a new table called 'Filtered Sales' that is the Sales table filtered to 1/15/2019 - 2/5/2019

[Anonymous]

Thank you so much! Your method worked for me