Graph with date shows months not selected through filter

[Paulien1989]

Hi everyone,

I'm currently struggling with visualizing data in a line graph where at some points, there is no data.

Speficially: I want to show the number of emails sent over time. On the y axis I have the total numbers of emails sent and on the x asis a date hierarchy. The number of emails sent is a measure:

# emails sent = CALCULATE(DISTINCTCOUNT('Email Result'[Email Send ID])+0)

The +0 was a trick I found on this forum, to show zero's instead of blanks where the count is 0 (or non existent). Without it, the line graph would just not show the months at all without data (the x axis would go from july to october, skipping august and september...).

The date is filtered on the last 12 calendar months.

Now, the graph looks like this:

It is still showing the months not belonging in the filtered date range! I tried all sorts of work arounds, but it keeps giving me the months I do NOT want to show. Anyone have a answer?

[Anonymous]

Had the same issue and found the solution in this thread, just replace the 0s with BLANK() in your measure being plotted, it worked for me

https://community.powerbi.com/t5/Desktop/Bar-chart-of-measure-x-axis-does-not-respond-to-date-slicer...

[MalJan19]

I was having the same problem, and I solved the issue by marking the table joined to my date as the date table. It works perfectly now!

[Greg_Deckler]

How are you doing the filtering to the last 12 months?

[Paulien1989]

I tried several filters:

- Visual relative filter Last 12 Months (calendar)

- Page relative filter Lat 12 Months (calendar)

- Date slicer selecting 1st may 2017 thru 30th april 2018

- Page / visual filter stating Date is on or after 1st may 2017 AND before 1st may 2018

All give this same result...

I now have a workaround where I used a Year-Month text field on the axis and I manually selected the months I want to show, sorting them by a year-month numeric index dragged into Tooltips. It's not dynamic though.... cannot use dynamic date filters this way.

Hoping for a correct and dynamic solution.

[v-yulgu-msft]

Hi @Paulien1989,

Is there an existing relationship between calendar table and fact table based on date column? If so, you just need to add [date] from calendar to slicer, also add it to X-axis of chart. When you select to show Last 12 months from relative slicer, the chart will be fitered dynamically.

Regards,

Yuliana Gu