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

5 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you so much! Your method worked for me

    • Lucho_MOB507's avatar
      Lucho_MOB507
      New Member
      3 years ago

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

  • 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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