Showing Continuous Years on a Chart

[richardburling]

Good Morning,

I have had a request from a client to display a line chart with three years worth of data on it, that is displayed in a continous line. So as one year ends, the next one starts. It also needs to be controlled by filter selection, so if you selected the financial year of 2020-21, then it would need to show the two previous years. 

I have been looking around the forums and youtube videos but cant seem to find a method that works for me. If you can offer any assistance, I would be very grateful.

Have a good day.

RB

[Ritaf1983 · Accepted solution]

Hi @richardburling  :
You should create a calendar table that will be related to the transactions table  like : 

The tutorial for creating a calendar is here :

https://www.youtube.com/watch?v=Lt_nNLQz0IE

Then on slicer put a year from the calendar table: 

Use the needed granularity as a hierarchy of the graph  (X-axis from date table again):

PBIX is attached

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

[Ritaf1983]

Hi @richardburling 
Do you mean something like that ?

if not please show a screenshot of the desired result.

[richardburling]

That's it!! Only my data is based on 52 weeks rather than 12 months but thats it. 

Can you please point to where I can find out how to build this type of chart? 

[Ritaf1983 · Accepted solution]

Hi @richardburling  :
You should create a calendar table that will be related to the transactions table  like : 

The tutorial for creating a calendar is here :

https://www.youtube.com/watch?v=Lt_nNLQz0IE

Then on slicer put a year from the calendar table: 

Use the needed granularity as a hierarchy of the graph  (X-axis from date table again):

PBIX is attached

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

[richardburling]

Hi, 

Thanks for this.

It seems that you have multi-selected the years to show the data on the chart. The client has requested that they only select one year and the historical years show automatically. This is where I am stumped with this. 

[Ritaf1983 · Accepted solution]

@richardburling 
You can use dax to manipulate the years.

please refer to the linked discussion:

https://community.fabric.microsoft.com/t5/Desktop/Show-last-two-years-of-selected-year/m-p/1586070#M...

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

[richardburling]

Thanks @Ritaf1983 for your help. I am not sure I will be able to use a disconnected Year table as there is already a DIM_Calendar table that the report is connected to and this is coming directly from an Oracle database. The report is already being filtered using the DIM_Calendar table so I am worried we may get some confusion. However, this will be useful for future reports. Thanks again.