Lastest week on drilling up to year

Hi, I have use case where i have year with week as drill down layer , but on drillling up to the year it sums up all the values of week … I have a use case where i only want to show the latest week record on drilling up to that year. How can I do that?

Hi @Mehak ,

You can set up the dynamic aggregation following the example here.

To just show the latest week, create a filter on your time field, use relative dates for the filter type, days for the time granularity and weeks for the period. Apply the filter to all visuals in the dashboard and then add it to the sheet.

Hope that helps.


1 Like

Hi @wstevens01 , On setting the latest week filter , on drill down that year , it is only showing latest week but use case is to on drill down year to week it shows all week but on drill up to year , instead of summing all the week data , it only shows last record of week.
How I can do that?

Hi @Mehak,

It sounds like you’d like to see the drill-up, from week to year, to automatically filter by the last week, is that what you’re looking for? To accomplish that, you’ll need to set up a dynamic date parameter that you change as a calculated field.

You can start by creating a custom menu action that you’ll see in the context menu (be sure to disable Drill Up / Drill Downs when you publish the dashboard to avoid end-user confusion). Use the technique here to change the parameter and change the aggregation period. See attached screen shots.

From there, you’ll need to update the filter value based on whether the parameter is Weeks or Years.

I do like the manual filter as a control, because it’s straight-forward and the end-user can change it to whatever they want. However, using parameters, you can change it for them, automatically.

Hope that helps.


Hi @wstevens01 , Thanks for the solution . But I am trying to achieve this in a pivot table and not able to change the aggregation period . Any insights on how i can change the aggregation period will be helpful.

Hi @Mehak , The drill-downs don’t work with Pivot tables - see here, but you can setup a stand-alone control with a parameter per the link above and chance the aggregation period. You still have to use a separate parameter to automatically change the filter value on when the period value goes from from weeks to year.

Hope that helps