Charting Current Year vs Avg

[jwjwjwjwjw]

I am trying to compare units sold by month for this year (or the currently selected year) to last year and also the 5 yr average.

I've managed to get the correct averages and filters set up, but I'm stuck on one thing. When I select 2017 on my slicer, it only displays up to the current months on the chart for all three measures. I'm sure there is an easy way to get the last year and avg measures to display all months, but I can't figure it out. I could always create a table with some dummy data, but maybe there is a better solution?

Thanks, 

jw

When I switch to a previous year where all 12 months are in the past, all 12 months appear

[v-huizhn-msft]

Hi @jwjwjwjwjw,

From your first screenshot, it shows the month from Jan to September rather than correct month, right? In your resource table, please check there are data of October, November and December? Do you mind share your .pbix with dummy data for further analysis?

Best Regards,
Angelia

[jwjwjwjwjw]

 Avg Example

Thanks for the reply, I attached an example pbix, it is simplified from the original but the issue still exists. 

The data appears properly in all cases except for when 2017 is selected on the slicer. The months that haven't taken place yet in the current year (oct/nov/dec) don't show up because there is no data yet for those months, and for 2017 that is correct.

The problem when 2017 is selected, is that it also removes the oct/nov/dec from the two historical measures, which have a full year of data, in addition to the "selected period" measure. 

When the year is switched to 2016 or before, everything works fine and all 12 months for all 3 measures display as intended. 

What I would like to end up with is the ability for the user to change the selected period measure as they see fit and compare that to the historical average. As this is an attempt to view the seasonality of sales, the user will want to see what the trend for the rest of the year is like as well. 

Let me know if this doesn't make sense, I will try to explain it further. 

Thanks for the help, 

jw

[v-huizhn-msft · Accepted solution]

Hi @jwjwjwjwjw,

Based on my understanding, you need to add the month from the 'data' table rather than 'dates' table.



Best Regards,
Angelia

[jwjwjwjwjw]

This worked! Thanks so much for taking the time to figure it out, I knew I had to be overlooking something!

Anywho, one interesting thing I noticed was that the first time I used the data table month instead of the date month, the table didn't update properly. I had to completely remove the original chart and create a new one, then it worked. I have noticed this every once in a while in multiple slicer scenarios, though it could be my preferred slicer of choice, the custom visual Chiclet Slicer.