Filter report up until the current week

[Dismissile]

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?

[v-huizhn-msft]

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]

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.

[v-huizhn-msft]

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