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 will not work. This particular dashboard is keeping track of IT Tickets, the field I want to filter for is dateEntered (which is the date the ticket is entered into the system) and I want to show information for tickets opened prior to 2 weeks ago. 

 

The formula I think is closest to my goal is:

= DATEADD(AllData[dateEntered],-14,DAY)
and I get the following error on the visual:

 

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

9 Replies

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

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    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 so much for a quick response!

       

      I have been working through this. Is the slicer what effects the date? I have completed all the steps you have shown and the date is not effected by the DAX formula.

      • v-chenwuz-msft's avatar
        v-chenwuz-msft
        Community Support
        4 years ago

        Hi Anonymous ,

         

        Works fine with my pbix.
        1 Ensure that the two tables have no relationship
        2 measure is placed in the filter pane of the date table visual

        Best Regards

        Community Support Team _ chenwu zhu

         

         

  • vapid128's avatar
    vapid128
    Solution Specialist
    4 years ago
    14_days_ago =
VAR __LAST_DATE = LASTDATE(DateTable[Date])
VAR __FIRST_DATE = FIRSTDATE(DateTable[Date])
return
AVERAGEX(
    DATESBETWEEN(
        DateTable[Date],
        DATEADD(__FIRST_DATE, -14, DAY),
        DATEADD(__LAST_DATE, -14, DAY)
    ),
    CALCULATE([WHATEVER YOU WANT TO CALCULATE])
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      What does the CALCULATE at the end of the DAX expression do exactly? Can you giva an example of something to "plug in" there please?

       

      Thank you

      • vapid128's avatar
        vapid128
        Solution Specialist
        4 years ago

        If you have a measure: lines = countrows(table)

        in the function, that would be "calculate[lines]" <-- measure name

         

        One more thing, the function is for daily average, if you want daily sum, change AVERAGEX to SUMX.