Forum Discussion

jwjwjwjwjw's avatar
jwjwjwjwjw
Advocate II
8 years ago
Solved

Charting Current Year vs Avg

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

 

 

 

 

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    Hi jwjwjwjwjw,

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



    Best Regards,
    Angelia

4 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    8 years ago

    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's avatar
      jwjwjwjwjw
      Advocate II
      8 years ago

       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's avatar
        v-huizhn-msft
        Microsoft Employee
        8 years ago

        Hi jwjwjwjwjw,

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



        Best Regards,
        Angelia