Multiple year data displayed chronologically

[WxShady13]

HELP!!! I have several years worth of data (i.e. Headcount, Terminations, etc.) all on the same table with a single date field. When I sort by Months obviously it will group all months data together and not distinguish by year. I need to actually display the data by week and if possible not make use of a Year slicer (i.e. just a rolling 12 months worth of data by week.) Right now when it is displayed I cannot get the visual (line graph) to distinguish between years, and stay within the confines of the visual (it pops up a scroll bar at the bottom). What can I do?

 

[CSmith]

Create a column concatenated Year&Week use that to sort it will always group by week of year and stay chronological...

 

[WxShady13]

The problem with doing that is it runs everything together (i.e. 201953). Is there a way to set it up where it would look like 2019-53 or can I now use it to sort the Week Number?

 

[sandy666]

merge these columns with delimiter -
PowerPivot
=[Year]&"-"&[Week]
or
PowerQuery
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(Source, {{"Year", type text}, {"Week", type text}}, "en-GB"),{"Year", "Week"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"YW")

 

[CSmith]

=YEAR&"-"&WEEK

It will still sort without the '-'

 

[WxShady13]

Sandy and CSmith thank you both for your help. The problem when I merge the columns is it still displays wrong. Also how do I avoid the scroll bar appearing?

 

[CSmith]

with your setup you need this =YEAR&"-"&RIGHT("0"&WEEK,2)
And scroll bar is dependant on amount of data points... Not sure what you want smaller chart or?

 

[sandy666]

still displays wrong

what is wrong? Order?

 

[sandy666]

with your setup you need this =YEAR&"-"&RIGHT("0"&WEEK,2)
And scroll bar is dependant on amount of data point

I agree

 

[WxShady13]

Thank you...I figured out why the order was wrong (I was not sorting by Year Week). As for data points I need to display a rolling 12 month timeframe. I have a column that determines what week # should display. I am space limited on this page and the customer does not want another page. I didnt know if there was a way to force the visual not to extend beyond the confines so that a scroll bar doesnt show, rather all the data is contained within the visual.