cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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!
1 ACCEPTED SOLUTION
Super User II
Super User II

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

View solution in original post

3 REPLIES 3
Community Support
Community Support

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.DAX.PNG

 

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.

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
Super User II
Super User II

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

View solution in original post

Anonymous
Not applicable

Thank you so much! Your method worked for me

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors