Multi-Year Timeline

[RobFlanders]

I am looking to present a sequence of data points each attached to a date. I am looking to group them by month and present them in date order. The dates start in May last year and end in June this year

Unfortunately when I try and present it using a line chart I can either view by year or by month - and when I view by month I cannot start the chart at the first month - it always starts in January. How can I filter by month and have months display consecutively, rather than merging them across years?

 

[robofski · Accepted solution]

@RobFlandersWhen you remove the year from the Date you are now asking to only see things in Month buckets so data from every Month of January will be groupd, every February etc, so in your case the values from May of 16 and May of 17 will be combined together in the 'May' data point.

If you stick with the Line chart with Year and Month levels selected and then use the Drill Down arrow you can go to the next level down.  Be sure you use the correct Drill Down option!!!

[RobFlanders]

@robofski Thanks for the response - it appears I need to do as you say and "Expand all down one level in the Hierachy". However, I now have an image that looks like this:

Something is clearly wrong with the sequencing of a number of the months - not sure why.

[robofski]

@RobFlanders  Any chance you can share a sample of the data, that looks strange? 

[RobFlanders]

@robofski No sorry - there's a reason the pictures are heavily cropped!

The plot appears to assume that January 2017 is directly after January 2016, and Dec 2016 is directly after Dec 2015. I'll try some alternative plots.

Thanks for your help!

[ericOnline]

I received similar goofy results when drilling down the date hierarchy.

What is the best way to handle this?

Thank you

[fhill]

Try this...   We have 'Month' as it's own column in our raw data as text labeled '01-Jan', '02-Jan', etc  (you can easy get Power BI to create this with copyign and splitting your data and using mass transforms.)  We then graph our data by (text) Month and use Year from our real formatted Date column as the legend.  Values is Price (or whatever you want to graph up and down).

P.S.  You only have 2 years right now, so colors aren't critical, but see below where we 'fade' from old to current then go alternative colrs for future.

Hope this helps,

Forrest