Year as legend on line chart

[tgjones43]

Hello

I am working with 15 minute time series data over multiple years. A screenshot of the chart is shown below, but I'd like to be able to use year as the legend so that the lines are overlayed. I'm currently using Date Time in the format DD/MM/YY HH:MM:SS on the X axis. Would greatly appreciate a solution!

[ray_aramburo]

Try placing a Year column in the legend and a Month column in the X-axis, that will give you the overlap you're looking for 🙂 

[tgjones43]

Hi @ray_aramburo yes it does give the overlap, but because the x axis is month only it summarises the data for each month. I need to show each 15 minute datapoint. So the X axis needs to be DD/MM HH:MM:SS, which I am not sure how to do...

[ray_aramburo]

Try creating a calculated/custom column that only includes DD/MM and the timestamp. Use that on the axis as a categorical field and then you'll be able to have the split. You can guide yourself with the "Add Column From Examples" with Power Query if you are not sure how to do it.

[gmsamborn]

Hi @tgjones43 

I was able to get around this problem by creating a new column with the same timestamp except the year is changed to 2020.  (I used 2020 since it is a leap year.)

- Duplicate DateTime column
- Split new column by delimiter (Space, left-most delimiter)
- Rename columns to _Date and _Time
- Create custom column ( Chart_Date )

=Text.Start([_Date], Text.Length([_Date]) - 1) &
"0 " &
Text.From([_Time])

- Delete _Date and _Time columns

You can use [Chart_Date] as the x-axis and your year column from your date table as Legend.

Since all dates show as 2020, I created a simple measure to be used as a tooltip.

Display Date = SELECTEDVALUE( 'Date'[Date] )

Let me know how this works for you.

let
    Source = MySource,
    #"Duplicated Column" = Table.DuplicateColumn(Source, "DateTime", "DateTime - Copy"),
    #"Changed Type" = Table.TransformColumnTypes(#"Duplicated Column",{{"DateTime - Copy", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "DateTime - Copy", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"DateTime - Copy.1", "DateTime - Copy.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"DateTime - Copy.1", "_Date"}, {"DateTime - Copy.2", "_Time"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Chart_Date", each Text.Start([_Date], Text.Length([_Date]) - 1) &
"0 " &
Text.From([_Time])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Chart_Date", type datetime}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"_Date", "_Time"})
in
    #"Removed Columns"