topic Re: Filtering for data 2 weeks prior the current date in DAX Commands and Tips

Filtering for data 2 weeks prior the current date

Anonymous — Fri, 17 Jun 2022 17:10:04 GMT

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

Re: Filtering for data 2 weeks prior the current date

tackytechtom — Fri, 17 Jun 2022 17:55:19 GMT

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/

Re: Filtering for data 2 weeks prior the current date

v-chenwuz-msft — Tue, 21 Jun 2022 10:12:01 GMT

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.

Re: Filtering for data 2 weeks prior the current date

Anonymous — Tue, 21 Jun 2022 20:31:36 GMT

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

Re: Filtering for data 2 weeks prior the current date

vapid128 — Tue, 21 Jun 2022 23:39:17 GMT

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]) )

Re: Filtering for data 2 weeks prior the current date

Anonymous — Wed, 22 Jun 2022 13:24:52 GMT

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.

Re: Filtering for data 2 weeks prior the current date

Anonymous — Wed, 22 Jun 2022 13:28:11 GMT

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

Re: Filtering for data 2 weeks prior the current date

vapid128 — Wed, 22 Jun 2022 17:26:31 GMT

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.

Re: Filtering for data 2 weeks prior the current date

AlliedSol_Liz — Wed, 22 Jun 2022 18:08:42 GMT

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.

Re: Filtering for data 2 weeks prior the current date

v-chenwuz-msft — Thu, 23 Jun 2022 07:42:32 GMT

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