Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

YOY Line Chart

Hi,   I have a table similar to below structure, let's call it Raw_Data.     My data has complete booking date values and contains duplicate booking dates.   Now, I want a line chart with ...
Need Help
v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

Hi Anonymous

I then add the "BookingDate" as axis and "year" as legend but the axis still shows the complete date even after changing the format as shown below. Also noticed that the BookingDate didn't show the heirarchy under Axis.

Click on dwon arrow of the "BookingDate", then you would see two options, "date" and "date hierachy".

As tested, if setting as above, it is not possible to add a trend line.

 

Best Regards

Maggie

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi v-juanli-msft,

 

Thank you for the suggestions. 

 

The date heirarchy has been resolved but my main issue right now is the missing trendline option and the horizontal scrollbar.

 

It looks like the issue was caused by "Categorical" data type on X-axis but currently it does not allow me to change the data type to "Continuous".

 

The first approach mentioned above which shows 2017 vs 2018 in full view (without a horizontal scroll bar) and has a trendline is the ideal view but what is missing is the filter functionality. 

 

Hopefully someone could provide a better way to show the desired YoY line chart in full view, with trendline and filter functionality.

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous

    How about copying the [date] column which is added to the axis of chart, then add this copied column in the silcer.

     

    Best Regards

    Maggie

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      v-juanli-msft, thanks for the help but find a workaround on this issue and below is the solution that works for me.

       

      Here's the solution that works for me.

       

      Added a New Measure using this formula

      Total Revenue = SUM (Raw_Data[Revenue])

      Then added another New Measure for sameperiodlast year

      Total Revenue (Previous) = CALCULATE([Total Revenue], SAMEPERIODLASTYEAR('Date'[Date]))

      Below shows YOY Line Chart with trendline and is filterable by dimensions in the table.

      Remaining issue is how can I offset the last year's data so the peaks and lows fall on the same day.

       

      Tried creating a New Column for offset date and use it to calculate last year's revenue but it can't be plotted against 'Date'[Date].