Filtering for data 2 weeks prior the current date

[Anonymous]

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 

[vapid128]

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]

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]

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.

[v-chenwuz-msft · Accepted solution]

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]

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]

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

[tackytechtom]

Hi @Anonymous ,

How about using the relative date function from the filter pane? 🙂

Let me know if this helps!

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[AlliedSol_Liz]

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.  

[Anonymous]

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