Display non-existing values as 0, but only for part of the data

[Anonymous]

Hello everyone,

I am having the following issue:

1. I have a date table containing dates going back a few years
2. The date table is connected to a sales table, listing customer, product, selling date, sales qty etc.
3. I have graphs in which I want to show the sales or sales growth per month per customer, however only for the past year in a bar chart (removing any months before that from the x-axis).
4. Many customers do not have sales every month.

By default, if I filter on one customer, any month in which this customer did not purchase anything will be omitted entirely from the chart, but I want the chart to display such months with value 0. By selecting "show items with no data" in the axis options or by adding +0 to a measure, I can get the chart to do this. This partially solves the problem, but not entirely, because now a 0 is displayed for each month in the date table, so I still need to somehow restrict the chart only to show the last 12 months of data.

If I place a filter directly on any field that is in the date hierarchy on the x-axis of the chart, it will actually remove the months that I filtered out, but I cannot place a relative date filter on any of these. If I add a separate date field to the "filters on this visual" part of the filter pane, then I am able to create a relative date filter on this, but now the months that I filter out in this way are not actually removed from the chart anymore (their values are only set to 0).

Now, my question: Is there any way I can get the data to display in such a way that only the last 12 months are shown, including any missing months in this period (as 0), and nothing else?

And as a bonus (somewhat related) question: is there any way for a chart with the sales per month for a customer to display every month between the first month that contains sales data and the present, including missing months as 0, but to remove any months from the chart before the first month that contains sales data? If there is a new customer, it does not look very nice to have a whole string of months with 0 in the chart leading up to the month in which the sales started.

Thanks!

[v-xuding-msft]

Hi @Anonymous ,

Can you please share a dummy file? Or please share some sample data and expected result. Currently, I can't understand the scenario clearly.

Please see this post regarding How to Get Your Question Answered Quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello Xue Ding,

Thank you for your reply. I have saved a dummy file below:

https://www.dropbox.com/s/opxeawnpuqbwxid/test.pbix?dl=0

What I want to achieve is the following: to get the bar chart to show the sales data for the past 12 months (so in this case Sep. 2018 - Sep. 2019), displaying any months in which no sales were recorded as 0. Any months before Sep. 2018 or after Sep. 2019 should not be displayed.

If I switch off "show items with no data", then all months that have no sales will be removed from the chart, including ones in the past year, which is not what I want. If i switch this option on however, every month in the date table (which covers 2018 and 2019) will be displayed, and I cannot get the months before Sep. 2018 and Sep. 2019 to disappear anymore with a relative date filter. So I can get either all of the months with 0 sales in the date able to display or none of them, but not just the ones in the past year.

Thanks!