Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Filtering for data 2 weeks prior the current date

I am trying to figure out how to filter a date field for 2 weeks prior the current date. The dashboard I am creating has to be completely automated, so using filter where I am selecting a spefic date...
DAX Measures
Filter
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi Anonymous ,

    First you need two separate tables, a date table and a fact table. They are not related to each other.

    Then create a measure, like the following.

    Measure =
VAR _selected =
    MAX ( Slicer[Date] )
VAR _start = _selected - 14
RETURN
    IF (
        SELECTEDVALUE ( 'Table'[Date] ) >= _start
            && SELECTEDVALUE ( 'Table'[Date] ) <= _selected,
        1
    )

    Then put the measure in filter pane and set it show items which is 1.

     

    The output:

     

    Pbix in the end you can refer.

    Best Regards

    Community Support Team _ chenwu zhu

     

    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
    4 years ago

    Thank you for the suggestion tomfox, but I need to filter for data that was created prior to 2 weeks ago. 

  • AlliedSol_Liz's avatar
    AlliedSol_Liz
    Frequent Visitor
    4 years ago

    How would you use this to only show the previous 2 weeks from the current date?  This is similar to something I need to create a table that will show information for the three years prior. Right now it would show sales total for each month in 2019, but I would the table to update to only show 2020 sales when we get into 2023.