cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dismissile
Regular Visitor

Filter report up until the current week

I have a table that is measuring the number of searches performed on a website. I created a report that lists the following:

 

  • Term
  • # Searches

 

I added a visual level filter on my Date to only show data that is in the last 7 days (not including today).

 

I need to be able to create the same exact visual in the same report to show the top 10 searches of all time - but I do not want it to include this week's data.

 

Is there any way to do this?

3 REPLIES 3
Microsoft v-huizhn-msft
Microsoft

Re: Filter report up until the current week

Hi @Dismissile,

Please create a new table by clicking "New Table" under Modeling on Home page. Get the top10 search of all time excluding this week.

NewTable=TOPN(10,FILTER(Table,Table[Date])<=DATEADD(Today(),-7,Day)),count(table[research]))


Create a relationship between the NewTable and your fact table, select the NewTable[research] in your visual, and check if it works.

If this is not what you want, please share your sample table for further analysis.

Best Regards,
Angelia

Dismissile
Regular Visitor

Re: Filter report up until the current week

Can you re-check your syntax here? I'm not getting this thing to "compile".

 

My tables in my model that are relevant are:

 

Date[Date]

 

Requests[Term]

Requests[Search Count] (measure)

Requests[Date]

 

There is a relationship between the date dimension and requests table.

Microsoft v-huizhn-msft
Microsoft

Re: Filter report up until the current week

Hi @Dismissile,

Could you please post your sample table or .pbix file and list the expected result for further analysis? My solution posted above didn't work?

Best Regards,
Angelia

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors