Thank you Suman for the quick reply, but it doesn't seem to be what I am looking for.

My focus is not so much the filter as such, ideally I would prefer it to remain dynamic, i.e. that the user can filter on specific months, quarters, etc.

What I need is that the table shows all months within that filter, including the month that have no data.

In the workbook you attached, if I select Q1 only, then the table displays only Jan and Feb. I would like March to be displayed as well as blank since there is no data.

Hello Zhouyi - thanks for taking the time to help.

I understand the work around to 'complete' my database with all missing month and that works perfectly and easily with the example I enclosed.

Unfortunately, I might have over simplified the example compared to my real data. Indeed, my data is a bit more complex in the sense that I have many more dimensions. To continue with the same example, let' say that my database includes further dimensions such as Market, Region, Key Account, Products. And I want the users to be able to filter on any of these dimensions and see the monthly data including the months without data. I have enclosed the updated workbook, but please consider that this is still a simplified version of the reality as I have thousands of products.

In order to use your workaround (if am I not wrong) I would have to create a calendar file that includes all possible combinations of Market, Region, Key Account, Products and Months to ensure that I capture all possible options. Plus, I would have to maintain that database for every new Product or change in Key Account.

Again I recon that your solution can work, but I am afraid it is not manageable with my real data.

Hi Zhouyi,

I cannot open your last workbook as I only have v10.3.

Anyway, I have replicated in the enclosed workbook what I think is a limitation of your proposal:

In this example I have sales covering all days of February on various products with exception of product BB. The joint with Calendar will not create a line for Feb for BB and therefore a filter on product BB (and Null) will show all months but February.

Please let me know if I am missing something?

One additional downside of your proposal is that the user always has to filter on his selection + 'Null' which is not very intuitive.

Thanks again,

Charles

Dear Zhouyi,

I am grateful, the "add to context" solves my issue.

Thanks a lot for your help and taking the time to explain the steps.

Charles