topic Re: Filter a table by date using DAX in Desktop

Filter a table by date using DAX

Anonymous — Sat, 03 Aug 2019 21:07:34 GMT

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!

Re: Filter a table by date using DAX

jdbuchanan71 — Sat, 03 Aug 2019 21:41:37 GMT

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

Re: Filter a table by date using DAX

v-eachen-msft — Mon, 05 Aug 2019 06:03:35 GMT

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.

Re: Filter a table by date using DAX

Anonymous — Mon, 05 Aug 2019 22:13:33 GMT

Thank you so much! Your method worked for me

Re: Filter a table by date using DAX

Lucho_MOB507 — Mon, 26 Sep 2022 20:49:00 GMT

Hello, is there any option using the filter parameter used in the report? Because i'm trying but the selected date not filter

Re: Filter a table by date using DAX

Anonymous — Wed, 20 Sep 2023 14:00:44 GMT

How can I create a measure to get the values for one specific date,

I created a variable for the date, but the calculate(measure, filter(date=variable date)) returns a blank result