Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Filter a table by date using DAX

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!
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    7 years ago

    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

v-eachen-msft's avatar
v-eachen-msft
Community Support
7 years ago

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.